google-sheetsgoogle-sheets-formula

Conditional criteria in a query formula


I'm writing a formula where information is pulled in from the named range SiteLevel. The formula should pull in the data based on the criteria in cell C2. I want to add in a condition that if C2 equals ALL, the criteria should be ignored and all rows be returned.

Screenshot

I've tried using query() and combining different WHERE and OR clauses with no luck.


Solution

  • Use this filter() pattern:

    =filter( 
      SheetX!A2:H, 
      (C2 = "ALL") + iferror(search(C2, SheetX!A2:A)), 
      (C3 = "") + iferror(search(C3, SheetX!E2:E))
    )
    

    The formula uses direct range references instead of the SiteLevel named range for clarity.

    See filter(), search() and iferror().