Search code examples
google-sheetsgoogle-sheets-query

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

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

Solution

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

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