FILTER with multiple criteria: AND, OR and no matches
Return all rows that satisfy two conditions, compare AND with OR, and handle an empty result without confusing it with blank source cells.
Download the example workbook (.xlsx)
Illustrative data. English function names and comma argument separators are shown; Excel may use different separators or translated function names. The file was structurally inspected, but native Excel calculation and UI behavior need local verification.
Compare AND and OR on the same four rows
The source is A2:C5: North/Open/50, North/Closed/30, South/Open/70 and South/Closed/20. An AND test uses multiplication:
=FILTER(A2:C5,(A2:A5="North")*(B2:B5="Open"),"No rows")
Only the first row qualifies. An OR test uses addition and checks whether the sum is positive:
=FILTER(A2:C5,((A2:A5="North")+(B2:B5="Open"))>0,"No rows")
Three rows qualify: both North rows and South/Open. A West/Open condition returns “No rows” through the if_empty argument. Without that argument, an empty array can show #CALC!.
Protect the spill area
Put each formula in a clear area outside an Excel Table, with enough empty cells to the right and below. The download spaces the output anchors at E2, I2 and M2 so each three-column result has room to spill. If you add source rows, extend each condition range to the same height as the source and leave enough output space. See how to clear a spill error.
Compatibility and source
Compatibility: Excel for Microsoft 365, Excel 2021 and Excel 2024 on desktop or web where the named function is supported. XLOOKUP is unavailable in Excel 2016 and Excel 2019. Dynamic-array results need an Excel version that supports spilling. Menu labels and available validation controls vary by desktop and web edition.
Formula and UI behavior are described in Microsoft’s FILTER guidance. Start with the broad formula and shortcut reference or browse more Excel tips.