Google Sheets query that searches multiple columns

Question

I currently have a search function I got from a tutorial and I'm wondering how I can modify it so that when I type key words into B1 it will search for the key words in columns A thru G on the workshop tab instead of just column D. I left the code I currently have below:

=QUERY(Workshop!$A$1:$G,"SELECT * WHERE LOWER(D) LIKE LOWER(""%" &JOIN("%"") AND LOWER(D) LIKE LOWER(""%", SPLIT($B$1," "))&"%"")",1)

Here is a link to the sample spreadsheet:

https://docs.google.com/spreadsheets/d/1nCNMpe2peJUWerUP8fgjMHxTiIwgUyZoM5GAW1PnoQo/edit?usp=sharing

I tried replacing the "D" with "A:G" and I also tried replacing it with "A,B,C,D,E,F,G" and neither worked. I don't know what else to try, I am still so new to this! I couldn't find anything on Google about it either!

player0 player0 · Accepted Answer · 2020-05-16T01:18:18

instead of searching through all columns you can collapse them into one and search it there and then not include it into the output:

=ARRAY_CONSTRAIN(IFERROR(QUERY({Workshop!A:G, 
 TRANSPOSE(QUERY(TRANSPOSE(Workshop!A:G),,99^99))}, 
 "where lower(Col8) contains '"&LOWER(B1)&"'", 1)), 99^99, COLUMNS(A:G))

ofc there are like 20+ levels of how strict or smart you want that search to be...

Google Sheets query that searches multiple columns

1 Answers