I am using google sheets and On the A column, I have the name of the Cities and on the B1, C1, D1 cells I have the name of the employees. "x" is marked under the name of the employees for the city they are responsible. Now I want to create a formula in cell B8 which would match the City name in A8 and return the name/names of the employees that are responsible. For example, for Florida both Adam and Calvin are responsible so I would like to see both of their names in cell B8. I tried using index and match but it only ends up showing the 1st match and doesn't return all the matches.

I tried using TEXTJOIN but I am stuck at this point. Can anyone please help me?

CodePudding user response：

=textjoin(", ",1,filter(B1:D1,filter(B2:D5,A2:A5=A8)="x"))