Google Sheets Query Filter by Multiple Variables

Question

Is there a better way of writing this formula out? I mean it works but there has to be a better way of doing it where I can have multiple Search parameters. I am using a search box to then that splits the cell with this formula =if(E1="","", if(REGEXMATCH(E1,";"),SPLIT(E1,";"),E1))

https://docs.google.com/spreadsheets/d/1-ymDylwRjd0zYnu3m0uxCz8hAavnL5Sxx39YrtlWylc/edit?usp=sharing Example Sheet

=if(E1="",QUERY(IMPORTRANGE("Spreadsheetidabcd", "RawData!A:Z"), "Select Col1,Col12,Col2,Col10,Col3 where (Col11='')",1),QUERY(IMPORTRANGE("Spreadsheetidabcd", "RawData!A:Z"), "Select Col1,Col12,Col2,Col10,Col3 where Col1 contains '"&$F$1&"' or Lower(Col12) contains Lower('"&$F$1&"') or Lower(Col12) contains Lower('"&$G$1&"')or Lower(Col10) contains Lower('"&$F$1&"')or Lower(Col10) contains Lower('"&$G$1&"')or Lower(Col2) contains Lower('"&$F$1&"')or Lower(Col2) contains Lower('"&$G$1&"')or Col3 contains '"&$F$1&"' or Col3 contains '"&$G$1&"'or Col4 contains '"&$F$1&"'or Lower(Col5) contains Lower('"&$F$1&"')"))

Keep in mind that we can't see what you are seeing. We don't know your end goal, your sheet layout, data, data types, etc. So you're showing us an inch of fur and asking us to guess the animal. Please share a link to your spreadsheet (or a copy of it). — Erik Tyler
– Erik Tyler, Commented Nov 9, 2021 at 17:29
Please edit E1 to contain a realistic search term you use; and please make sure that search term contains a semicolon. Your post says, "... it works..." I would like to see it working as you intend with a search term instead of a blank search. — Erik Tyler
– Erik Tyler, Commented Nov 9, 2021 at 17:58
Erik Tyler I put search terms in there for you. currenly i have it set up so i can only do 2 Variables but I would like to have it static and do how ever many as I put in — Jacob Lenertz
– Jacob Lenertz, Commented Nov 9, 2021 at 18:02
You currently have two search terms: Thrall and Illidan. Your search returns three line items. Yet I see nine line items that match one of those terms in your full data set. Can you explain why your return count is only three and not nine? — Erik Tyler
– Erik Tyler, Commented Nov 9, 2021 at 18:53

NightEye · Accepted Answer · 2021-11-09 19:58:22Z

1

You can try this one, although this returns the row if any of the columns contain the values of either F1 or G1 (I assume that's what your search box is for, to find the rows containing the search term).

Formula:

=if(E1="",
QUERY(IMPORTRANGE("Spreadsheetidabcd", "RawData!A:Z"), "Select Col1,Col12,Col2,Col10,Col3 where (Col11='')",1),
arrayformula(split(query({transpose(split(textjoin(",", true, QUERY(IMPORTRANGE("Spreadsheetidabcd", "RawData!A:Z"), "Select Col1, Col12, Col2, Col10, Col3, ';' label ';' ';'")), ",;,",))}, "where lower(Col1) contains lower('"&$F$1&"') or lower(Col1) contains lower('"&$G$1&"') or Col1 contains 'Timestamp'"), ",")))

Basically, what the formula does step by step is:

It appends a column that marks the end of the row when we combine them into 1 column.
Combines all columns into 1 using , as delimiter (, marks per column and ; marks per row)
Then we split them by ,;, to separate each rows (each row contains the textjoined result)
After that, we filter the data using query where a row should contain the values of F1 and G1 (and including Timestamp to include the header).
Filtered data will then split back to their original form

Output:

EDIT:

To be dynamic, I modified it to check F1:1 range instead.

Formula:

=if(E1="",
QUERY(IMPORTRANGE("Spreadsheetidabcd", "RawData!A:Z"), "Select Col1,Col12,Col2,Col10,Col3 where (Col11='')",1),
arrayformula(split(query({transpose(split(textjoin(",", true, QUERY(IMPORTRANGE("Spreadsheetidabcd", "RawData!A:Z"), "Select Col1, Col12, Col2, Col10, Col3, ';' label ';' ';'")), ",;,",))}, "where lower(Col1) contains lower('"&join("') or lower(Col1) contains lower('", filter($F$1:$1, not(isblank($F$1:$1))))&"') or Col1 contains 'Timestamp'"), ",")))

Output:

edited Nov 9, 2021 at 19:58

answered Nov 9, 2021 at 19:14

NightEye

11.3k2 gold badges8 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Jacob Lenertz Over a year ago

Perfect is there away to make it static as well so i can have more different number of search terms?

NightEye Over a year ago

I think you mean dynamic @JacobLenertz. I'll update my answer.

NightEye Over a year ago

I have now updated the answer. See EDIT section @JacobLenertz

Collectives™ on Stack Overflow

Google Sheets Query Filter by Multiple Variables

1 Answer 1

Formula:

Output:

EDIT:

Formula:

Output:

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Formula:

Output:

EDIT:

Formula:

Output:

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related