Google Sheets – Shortcut for Multiple Columns in Query

google sheetsgoogle-sheets-query

What's the shortcut for

=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 Stack Overflow

Best Answer

Add a column (L for example) to count whether "N1" features in any of the columns of interest, say with:

 =countif(B4:K4,"*"&N$1&"*")

copied down to suit and use that in the query:

=query(A:L,"select A where L>0",3)
Related Topic