Search code examples
arraysgoogle-sheetsgoogle-sheets-formulamatchgoogle-query-language

Google Sheets Query - Not like partial match


So I have this formula, and it's working as intended but I would like to further refine the data.

Formula:

=QUERY('Users'!A1:Q, "Select A,B,C,F,G,O,Q where Q >= 180 and Q < 44223")

I have tried:

=QUERY('Users'!A1:Q, "Select A,B,C,F,G,O,Q where Q >= 180 and Q < 44223 and F not like '*text*'")

But that caused an error. Ideally, I would like to ommet any results that match partial texts in columns C and F. Any ideas?

EDIT: Included a link to an example sheet

Ideally, I want the query to match everything in column F except 'Archived' (but needs to be wildcarded) and everything in column C except Delta (again, needs to be wildcarded)


Solution

  • try:

    =QUERY(Sheet1!A1:Q6, 
     "select A,B,C,F,G,O,Q 
      where Q >= 180 
        and Q < 44223 
        and not lower(O) matches '.*archived.*' 
        and not lower(C) matches '.*delta.*'")