CODEWITHSUNDEEP
regexgoogle-sheetsgoogle-sheets-formulaarray-formulasgoogle-sheets-query
IMTheNachoMan
IMTheNachoMan

Reputation: 5811

get values from first column when other columns are true using a lookup list

I have a source table with a variable number of columns. The first column is a name, and the remaining columns are type with values of true or false based on my data.

I also have an input lookup column that will have the names of the types.

Based on the types selected in the lookup column, I want to return the names from the source column that has true for any of the selected types.

It is hard to explain so I made a sample sheet with expected output and explanation at https://docs.google.com/spreadsheets/d/1U7-Vz8tq-4E1Z6jkVFzsoIvW8VHRJNGw0oqDE1LUKOI/edit?usp=sharing has sample input and my expected output.

New rows could be added to the source table, and new columns (types) could be added too. One thought I was to have a formula for each row in the source table checking the column based on what is in the lookup table but I want to avoid that because new rows/columns could be added to the source table.

I'm hoping for a single arrayformula or something efficient. I have been looking at this for hours with no luck. I don't even know where to start...

Upvotes: 1

Views: 124

Answers (1)

player0
player0

Reputation: 1

for H9:

=ARRAYFORMULA(UNIQUE(TRIM(QUERY(SPLIT(TRANSPOSE(SPLIT(QUERY(TRANSPOSE(QUERY(TRANSPOSE(
 IF(B3:F=TRUE, "♠"&B2:F2&"♦"&A3:A, )),,999^99)),,999^99), "♠")), "♦"), 
 "select Col2 where Col1 matches '"&TEXTJOIN("|", 1, G9:G)&"'"))))

0

for H3:

=ARRAYFORMULA(UNIQUE(TRIM(QUERY(SPLIT(TRANSPOSE(SPLIT(QUERY(TRANSPOSE(QUERY(TRANSPOSE(
 IF(B3:F=TRUE, "♠"&B2:F2&"♦"&A3:A, )),,999^99)),,999^99), "♠")), "♦"), 
 "select Col2 where Col1 matches '"&TEXTJOIN("|", 1, G3:G6)&"'"))))

Upvotes: 1

Related Questions