CODEWITHSUNDEEP
sqlgoogle-sheetsgoogle-query-language
BCLC
BCLC

Reputation: 139

QUERY - One string in several columns

While there's no IN in google sheets unlike in SQL, I'm not looking for several strings in one column. I'm looking for one string in several columns.

So what's the shortcut of

=QUERY($A:$K, "select A where B contains '"&N1&"' or C contains '"&N1&"' or D contains'"&N1&"' or E contains '"&N1&"' or F contains '"&N1&"' or G contains '"&N1&"' or H contains '"&N1&"' or I contains '"&N1&"' or J contains '"&N1&"' or K contains '"&N1&"'",3)

?

I'm hoping for something like

=QUERY($A:$K, "select A where B,C,D,E,F,G,H,I,J,K contains '"&N1&"'",3)

Cross-posted to Web Applications

Upvotes: 0

Views: 274

Answers (1)

Tom Sharpe
Tom Sharpe

Reputation: 34220

OK two ways you might try

An array formula which tots up the number of matches in each row

=query({A:A,arrayformula(mmult(n(isnumber(find(N1,B:K))),transpose(column(B:K))^0))},"select Col1 where Col2>0")

or a hacky way which is like the workaround for a join in the reference in your question

=query(A:K,"select A where B contains '"&N1&"'" & arrayformula(concatenate(" or "&char(column(C:K)+64)& " contains '"&N1&"'")))

(works for single-letter column references only but may be quicker than the first one).

Upvotes: 1

Related Questions