CODEWITHSUNDEEP
google-sheetsformula
Jacob Lenertz
Jacob Lenertz

Reputation: 81

Google Sheets Query Filter by Multiple Variables

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

Upvotes: 1

Views: 1644

Answers (1)

NightEye
NightEye

Reputation: 11184

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

Upvotes: 1

Related Questions