Google Sheets QUERY Function – Matches Empty Cell

google sheetsgoogle-sheets-query

=query(A1:S10,"select C,D,E,F,H,K,P,R,S WHERE Q='USA' AND R matches 'Florida|NY|'''

I would like to add (or empty cell) to the 'Florida|NY'
That is to say I would like to select rows with the Q= 'USA' and R matches either 'Florida','NY' or empty

Best Answer

Add OR R is null to your SELECT statement.

Google-sheets – Google Spreadsheets Query( “where a matches ‘/[A-Z+]/’ ”) condition

Note that this is not a global search...

This sentence explains what is going on: unlike the match in regexmatch, the matches clause of Query language requires the entire string to match the given regular expression. So, if you want to match strings where some part matches a regular expression re, the regular expression should be wrapped in .*:

select * where A matches '.*re.*'

Google Sheets – Query Syntax Using Cell Reference

Please try syntax of this kind:

=QUERY(Responses!B1:I, "Select B where G contains '"&$B1&"'")

Best Answer

Related Solutions

Google-sheets – Google Spreadsheets Query( “where a matches ‘/[A-Z+]/’ ”) condition

Google Sheets – Query Syntax Using Cell Reference

Related Topic