Search across multiple columns for a value(s) return multiple row results

Question

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.

Answer 1

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' ")))

---

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&"' ")))

---

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&"' ")))

---

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