0

I am a rookie at this and I spent hours trying to figure this out but can find anything that really helps.

Scenario 1:
Looking at the photo.
I want to search columns C through M for a selected color(s)
In this case Red.
Return all the rows (Column A and B only) that have red in one of the row's columns.

Result should be:

Tim | Tim@
Bob | Bob@
etc.

Scenario 2:
Looking at the photo.
I want to search columns C through M for a selected color(s)
In this case Red and Blue
Return all the rows that have red or blue in one of the row's columns.
Result should be all the names and emails.

I will input this on a separate sheet and be able to add new values if I add new rows on the main sheet.

enter image description here

1 Answer 1

0

There are two key elements:

  • A2:A10&"|"&B2:B10

    • join the name and email BEFORE the query
  • use of ARRAYFORMULA(TEXTJOIN).

    • For example in the search for 'Red', the formula includes this code: =TEXTJOIN("OR ", 1, "Col"&ROW(A2:A10)&" = 'Red' ") (inside ARRAYFORMULA) which generates:

Col2 = 'Red' OR Col3 = 'Red' OR Col4 = 'Red' OR Col5 = 'Red' OR Col6 = 'Red' OR Col7 = 'Red' OR Col8 = 'Red' OR Col9 = 'Red' OR Col10 = 'Red'


Match 'Red" - hard coded

=arrayformula(QUERY({A2:A10&"|"&B2:B10,C2:K10}, "select Col1 where "&TEXTJOIN("OR ", 1, "Col"&ROW(A2:A10)&" = 'Red' ")))

hardcoded


Match one value ('Red') as a variable

arrayformula(QUERY({A2:A10&"|"&B2:B10,C2:K10}, "select Col1 where "&TEXTJOIN("OR ", 1, "Col"&ROW(A2:A10)&" = '"&$D$19&"' ")))

variablered


Match EITHER of two values ('Red' or 'Blue') as variables

=arrayformula(QUERY({A2:A10&"|"&B2:B10,C2:K10}, "select Col1 where "&TEXTJOIN("OR ", 1, "Col"&ROW(A2:A10)&" = '"&$D$28&"' ")&" OR "&TEXTJOIN("OR ", 1, "Col"&ROW(A2:A10)&" = '"&$D$29&"' ")))

two variables


Hat tip: @player0 Google Sheets Query sum indeterminate number of columns-StackOverflow

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.