TL;DR —
FILTERanswers a different question thanVLOOKUP. A lookup returns one match;FILTERreturns every row that meets your condition and spills the result into a range that updates itself. Syntax:=FILTER(array, include, [if_empty]). Three things trip everyone up: when nothing matches you get#CALC!unless you supply the third argument (=FILTER(data, cond, "No match")); to combine conditions you multiply arrays for AND and add them for OR ((A=x)*(B=y)), neverAND()/OR(); and a blocked spill range throws#SPILL!. Available in Excel 365 and Excel 2021+.
=FILTER(Sales, Region = "West")
=FILTER(Sales, (Region = "West") * (Amount > 1000), "No match")
For twenty years, pulling "all the rows where region = West" meant a helper
column, AutoFilter, and a copy-paste — or a fragile CSE array formula. FILTER
turns that into one expression that recalculates the instant the source data
changes. But because it returns an array instead of a single value, it behaves
in ways a lookup never did, and the error messages are new too.
What you'll learn
- Why
FILTERis a query (return many) and a lookup is a find (return one) - The AND / OR trick: multiply and add arrays — not
AND()andOR() - Why an empty result throws
#CALC!, and the one argument that fixes it - What actually causes
#SPILL!and how to clear it - The "grail formula"
=SORT(UNIQUE(FILTER(...)))for live, clean lists
The mental model: FILTER is a query, not a lookup
If you've used SQL, FILTER is the WHERE clause: give me every row where this
is true. VLOOKUP and XLOOKUP are the opposite —
they scan top to bottom and stop at the first hit. That single difference
explains every surprise FILTER throws at you. A lookup returns one value into
one cell. FILTER returns a block of rows and spills them down (and
across) from the cell where you typed the formula. You don't drag it; you don't
wrap it in CSE; you write it once and Excel sizes the output to the data.
' One match, one cell:
=XLOOKUP("West", Region, Sales) ' the first West row
' Every match, spilled into a range:
=FILTER(Sales, Region = "West") ' all West rows, as many as there are
Think of the second argument, include, as a column of TRUE/FALSE the same
height as your data. Region = "West" doesn't compare one cell — it compares
the whole Region column at once and produces an array of TRUE/FALSE. FILTER
keeps the rows where the value is TRUE.
The rule that unlocks everything: multiply for AND, add for OR
This is the single most useful thing to know about FILTER, and most tutorials
bury it. Your instinct is to write AND(Region="West", Amount>1000). It won't
work — AND() collapses the whole array down to one TRUE/FALSE, so FILTER
either returns everything or nothing. The include argument has to stay an
array. So you do the boolean math yourself:
' AND — multiply the conditions (TRUE*TRUE = 1, anything*FALSE = 0)
=FILTER(Sales, (Region = "West") * (Amount > 1000), "No match")
' OR — add the conditions (FALSE+FALSE = 0, anything else > 0 = kept)
=FILTER(Sales, (Region = "West") + (Region = "East"), "No match")
Multiplication is logical AND because TRUE*TRUE = 1 and any *FALSE = 0.
Addition is logical OR because a row survives if either term is 1. Wrap each
condition in parentheses — the comparison must happen before the math. Once this
clicks, arbitrarily complex criteria are just arithmetic on arrays of 1s and 0s.
Why FILTER throws #CALC! — and the argument that prevents it
Here is the number-one "FILTER not working" report. When no row matches, the
result is an empty array, and Excel has no way to display "nothing" in a cell —
so it returns the #CALC! error. This is not a bug in your logic; it's what
an empty array looks like. The fix is the optional third argument, if_empty:
=FILTER(Sales, Region = "North") ' -> #CALC! if no North rows
=FILTER(Sales, Region = "North", "No match") ' -> "No match" (clean)
=FILTER(Sales, Region = "North", "") ' -> blank, if you prefer
Make if_empty a habit on every FILTER that feeds a dashboard or another
formula. A surprise #CALC! cascades — anything referencing the spill inherits
the error. Supplying if_empty turns "no results" into a value you control
instead of a red error that breaks the rest of the sheet.
#SPILL! is about the neighbours, not the formula
FILTER needs empty cells to spill into. If anything — a stray value, a merged
cell, a leftover label — sits in the range the result wants to occupy, Excel
can't lay the array down and returns #SPILL!. The formula itself is fine;
the destination is blocked. Click the cell, and Excel highlights the spill
range it's trying to use. Clear whatever is sitting in it, and the result
appears. Two practical rules: never put a FILTER directly above existing data,
and don't fill a whole column (A:A) with anything that would land in a spill
zone.
Reference a spilled result with the # operator
Because the output size changes with the data, you don't reference it with a
fixed range. You point at the spill with the # operator: if your FILTER
is in E2, then E2# means "the whole spilled range, however big it is right
now." That's how you feed a chart, a COUNTA, or a dropdown that should grow and
shrink automatically:
=COUNTA(E2#) ' how many rows FILTER returned, live
=SUM(F2#) ' total of a spilled amount column
The grail formula: SORT, UNIQUE, FILTER together
The reason FILTER matters so much is that it composes. Nest it inside
UNIQUE and SORT
and you get a single, self-maintaining formula that most people build with three
manual steps:
=SORT(UNIQUE(FILTER(Customer, Region = "West")))
Read it inside-out: FILTER pulls the West customers, UNIQUE removes
duplicates, SORT alphabetises — and the whole thing re-runs the moment a sale
is added. This is the canonical source for a dynamic dropdown or a dashboard
list. No helper columns, no refresh button, no VBA.
The judgment call: when FILTER replaces the old way
If you catch yourself adding a helper column and turning on AutoFilter just to
copy out matching rows, that's the signal to use FILTER instead. If you're
typing a {=...} CSE array formula to extract a subset, that pattern is a decade
out of date on 365. The honest exception: FILTER is volatile-ish on very large
sheets and recalculates with the workbook, so for a one-off extract from a
million rows, AutoFilter or Power Query is still leaner. For everything that
needs to stay live, FILTER wins.
How ExcelMaster helps
A lot of FILTER work is really "build me the live view": all unpaid invoices
past due, sorted by amount, on its own tab. ExcelMaster writes that
formula — including the AND/OR boolean math and the if_empty guard — from a
plain-English description, then places it where you want it and checks the spill
range is clear. You stay in control of the logic; it handles the array
mechanics that make FILTER finicky by hand.
Frequently asked questions
Why does my FILTER formula return #CALC!?
Because no row matched your condition, and an empty array can't be shown in a
cell. Add the third argument, if_empty: =FILTER(data, condition, "No match").
That replaces the error with a value you choose.
How do I filter on multiple criteria in Excel?
Keep the include argument as an array and do boolean math: multiply conditions
for AND — (A=x)*(B=y) — and add them for OR — (A=x)+(B=y). Don't use the
AND() or OR() functions; they collapse the array to a single value.
What causes the #SPILL! error with FILTER?
Something is blocking the cells the result wants to spill into — a value, a merged cell, or formatting in the spill range. Click the formula cell to see the highlighted spill range, clear whatever sits in it, and the result appears.
Does FILTER work in Excel 2016 or 2019?
No. FILTER and the other dynamic-array functions require Excel 365 or Excel
2021+. On 2016/2019 you'd use AutoFilter, a CSE array formula, or Power Query
instead.
Tested in
Tested in: Excel 365 (Windows 11) — last verified 2026-06-16.
Related guides: Excel UNIQUE · Excel SORT · XLOOKUP in Excel