CODEWITHSUNDEEP
google-sheetsgoogle-sheets-query
KeepCool
KeepCool

Reputation: 565

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

Upvotes: 0

Views: 1643

Answers (1)

Ed Nelson
Ed Nelson

Reputation: 10259

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

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

Upvotes: 1

Related Questions