Google Sheets Query Filter by Multiple Variables

1
votes

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&"')"))

1
google-sheetsformula
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
I have added a link to a copy of the sheet - Jacob Lenertz
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 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
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

1 Answers

1
votes

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:

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:

output2