Link to home
Create AccountLog in
Avatar of Anthony Pangilinan
Anthony PangilinanFlag for United States of America

asked on

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

User generated imageUser generated image
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).
Avatar of Professor J
Professor J

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))
Avatar of Anthony Pangilinan

ASKER

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.
User generated image
Can you please share the dummy file link here, so that I build the formula there?
ASKER CERTIFIED SOLUTION
Avatar of Anthony Pangilinan
Anthony Pangilinan
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account