FILTER Function

Returns every row that meets your conditions — and updates live.

Category

Lookup & Reference

Available in

Microsoft 365, Excel 2021+

Returns

A spilled array of matching rows

Taught hands-on in Level 4: Advanced Dynamic Arrays & Logic · Dynamic Arrays module

Overview

FILTER is like AutoFilter in a formula. Give it your data and a TRUE/FALSE test, and it spills every row that passes into the cells below and to the right.

Unlike VLOOKUP or XLOOKUP, which return the first match, FILTER returns all matches. Change the data or the criteria and the results update instantly — no clicking, no refreshing.

Syntax

=FILTER(array, include, [if_empty])

Arguments

ArgumentRequired?What it does
arrayYesThe data to filter — one column or a whole table.
includeYesA TRUE/FALSE test the same height (or width) as array, such as `B2:B6="East"`.
if_emptyOptionalWhat to show when nothing matches. Without it you get #CALC!.

Examples

Example 1 — All rows for one region

Show every sale from the region typed in G1.

E3fx=FILTER(A2:C7, B2:B7=G1)
ABCDEFG
1RepRegionSalesRegion:East
2AnaEast400
3BenWest350AnaEast400
4CaraEast520CaraEast520
5DevWest275EliEast610
6EliEast610
7FayNorth330
One formula in E3 spills all three East rows. Change G1 to West and the list rewrites itself.
=FILTER(A2:C7, B2:B7=G1) Explain
Leave empty cells below and to the right of the formula so it has room to spill.

Example 2 — Two conditions (AND)

East region and sales over 450. Wrap each test in brackets and multiply them:

=FILTER(A2:C7, (B2:B7="East") * (C2:C7>450), "No matches") Explain

Each test produces TRUE/FALSE (1/0). Multiplying gives 1 only when both are true, so this returns Cara and Eli.

Example 3 — Either condition (OR)

East region or sales over 450. Add the tests instead:

=FILTER(A2:C7, (B2:B7="East") + (C2:C7>450)) Explain

Adding gives 1 or more if either test is true. Here it returns Ana, Cara and Eli (no West or North rep is over 450).

Example 4 — Sorted results

Wrap FILTER in SORT to rank the matches — East reps by sales, highest first.

=SORT(FILTER(A2:C7, B2:B7="East"), 3, -1) Explain

SORT's second argument is the column to sort by (3 = Sales) and -1 means descending: Eli, Cara, Ana.

Example 5 — Only the columns you need

Return just Rep and Sales, skipping Region, by combining FILTER with CHOOSECOLS.

=CHOOSECOLS(FILTER(A2:C7, B2:B7="East"), 1, 3) Explain

Example 6 — Partial text matches

FILTER has no wildcards, but SEARCH can find text anywhere in a cell. Show reps whose name contains "a":

=FILTER(A2:C7, ISNUMBER(SEARCH("a", A2:A7)), "None") Explain

SEARCH returns a position number when it finds the text and an error when it doesn't; ISNUMBER turns that into TRUE/FALSE.

Common mistakes

  • #CALC! errors. Nothing matched. Add an if_empty value like "None".
  • #SPILL! errors. Something is in the way of the results. Clear the cells below/right of the formula.
  • #VALUE! errors. The include range is a different size from array — e.g. A2:C7 with B2:B10.
  • Using AND() or OR(). These collapse the whole range into a single TRUE/FALSE. Use * for AND and + for OR.

Notes

Kyle Stott

Author

Kyle Stott

Certified Microsoft Excel Expert

Kyle has worked professionally with Microsoft Excel for over a decade and has consulted on and taught Excel best practices to over 400 companies across 30+ countries.