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.
I've tried using query()
and combining different WHERE
and OR
clauses with no luck.
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.