CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formulatransposearray-formulasgoogle-sheets-query
Reneshade
Reneshade

Reputation: 11

Google Sheets query that searches multiple columns

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!

Upvotes: 1

Views: 1790

Answers (1)

player0
player0

Reputation: 1

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

0

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

Upvotes: 1

Related Questions