CODEWITHSUNDEEP
google-sheetsspreadsheet
Rafflezs
Rafflezs

Reputation: 13

How to return multiple data separated with comma in Google Sheet?

I have a sheet with two folders: Teachers and Discipline.

I'm trying to get the Discipline.ID at the Teacher.Discipline cell by using the following function:

                   =JOIN(",";FILTER(Discipline!A:A;Discipline!C:C=B2))

as you can see it in this sample spreadsheet.

The problem is, it works for Disciplines with only one Teacher, but when I try to filter the Teacher.Name at Discipline.Teacher with multiple teacher, it either returns the Discipline.ID when the name appears before the comma, or returns a NULL value if the name is after the comma (at Discipline.Teacher).

Is there any function that can return these values without this comma issue? (Needs to be specifcally in Google Sheet)

Upvotes: 1

Views: 122

Answers (1)

JPV
JPV

Reputation: 27242

In C2 try

=JOIN(",";FILTER(Discipline!A:A; regexmatch(Discipline!C:C; B2)))

and fill down.

Hope that helps?

Upvotes: 1

Related Questions