Query sheet range and return multiple results as string in one cell

1.5k views Asked by At

I have 2 columns, "Responsibility" & "Job".

I want to find a way to query for a "Job" and return all the "responsibilities" that have that "Job" title in one cell.

So searching for "Office Administrator" would result in -> "Answer Phones, Order office supplies", including the comma.

So far I'm only able to return one result ("Answer phones") using a mix of INDEX and MATCH:

=IFERROR(INDEX(A2:A5,MATCH("Office Administrator",B2:B5,0)),"")

enter image description here

1

There are 1 answers

1
Ed Nelson On BEST ANSWER

In C2 put the search term (Office Administrator). In D2 put this formula.

=arrayformula(textjoin(", ",true,if(B2:B=C2,A2:A,"")))