We help IT Professionals succeed at work.

Can I populate a Google Sheet column using a query that finds all the matches from another sheet?

Sheet1Sheet2
I have two Google Sheets. Sheet1 is categorizing the "Places" from Sheet2. I want to populate the "Categories" column on Sheet2 with all the headers from Sheet1 whose columns contain the given "Place". For example, the "Categories" cell for "Alabama" should contain "Bands, States" (or whatever concatenation format can be done).
Comment
Watch Question

Professor JMicrosoft Excel Expert
Top Expert 2014

Commented:
this is the formula to put in Sheet2 B2

=TextJoin(",",True,FILTER(Sheet1!$A$1:$C$1,INDEX(Sheet1!$A$2:$C$20,MATCH(A2,Sheet1!$A$2:$A$20))=A2))

Author

Commented:
Thanks for the reply! That seems to be working properly for cases when the "Place" occurs on the same line in multiple columns, but not when it is on different lines between columns. "Alabama" looks like the only case it's completely working on.
function1.PNG
Professor JMicrosoft Excel Expert
Top Expert 2014

Commented:
Can you please share the dummy file link here, so that I build the formula there?
Commented:
The biggest problem I ran into was being able to look across a range greater than a single row or column. ARRAYFORMULA allowed me to do that.

=TextJoin(",",True,ARRAYFORMULA(if(Sheet1!$A$2:$C$20=A2,"true","")))

Open in new window