Learn Excel
EN
EnglishENNederlandsNL

Home › Excel functions › FILTER

Excel FILTER Function: Filter Formula with Multiple Criteria

FILTER returns all rows from a range that meet a condition, and the result updates automatically when the data changes. Where VLOOKUP and XLOOKUP return one match, FILTER returns every match.

FILTER · 2 min read
In this guide
  1. FILTER syntax
  2. Basic example
  3. Multiple criteria
  4. When nothing matches
  5. Sort and count results
  6. Common errors
  7. FAQ

FILTER syntax

=FILTER(array, include, [if_empty])
ArgumentWhat it means
arrayThe range to return rows from.
includeThe condition, written as a comparison on a column, such as A2:A6="North".
if_empty
(optional)		What to show when no rows match, such as "No results".

Availability: FILTER works in Microsoft 365, Excel 2021, Excel 2024 and Excel for the web. Older versions show #NAME?.

Basic example: all sales for one region

ABC
1RegionRepSales
2NorthAnna1,200
3SouthBen800
4NorthChris950
5EastDana450
6SouthAnna700
=FILTER(A2:C6, A2:A6="North")

Type this in one cell, say E2, and Excel "spills" the result into the cells below and to the right:

EFG
2NorthAnna1,200
3NorthChris950

Add a new North row to the source and it appears in the result automatically. Put the condition in a cell to make it a dropdown-style report: =FILTER(A2:C6, A2:A6=H1).

Multiple criteria: AND and OR

Put each condition in brackets and combine them with * for AND, or + for OR.

  • AND: South region and sales above 750:
    =FILTER(A2:C6, (A2:A6="South")*(C2:C6>750)) returns Ben's 800.
  • OR: North or East:
    =FILTER(A2:C6, (A2:A6="North")+(A2:A6="East")) returns Anna, Chris and Dana.

This works because TRUE counts as 1 and FALSE as 0: multiplying only gives 1 when both are true, adding gives at least 1 when either is.

When nothing matches

If no rows meet the condition, FILTER returns #CALC!. Add the third argument to show a message instead:

=FILTER(A2:C6, A2:A6="West", "No results")

Sort, count or sum the results

  • Sort the result by sales, highest first: =SORT(FILTER(A2:C6, A2:A6="North"), 3, -1)
  • Return only some columns: filter just the reps and sales with =FILTER(B2:C6, A2:A6="North")
  • Count the matches: =ROWS(FILTER(A2:C6, A2:A6="North"))
  • Unique values from the filtered list: wrap it in UNIQUE.

Common errors

  • #SPILL!: there's something in the cells where the result wants to go. Clear them.
  • #CALC!: nothing matches. Add an if_empty value.
  • #VALUE!: the include range has a different height than array.
  • #NAME?: your Excel version doesn't have FILTER.

Frequently asked questions

What's the difference between FILTER and the filter button?

The filter button (Data › Filter) hides rows in the original table. The FILTER function creates a separate, live copy of the matching rows somewhere else, leaving the original untouched.

Can FILTER use wildcards?

Not directly. To filter on partial text, use ISNUMBER and SEARCH: =FILTER(A2:C6, ISNUMBER(SEARCH("an", B2:B6))) returns every rep whose name contains "an".

Can FILTER return results from another sheet?

Yes. The ranges can be on any sheet, for example =FILTER(Data!A2:C100, Data!A2:A100="North").

How is FILTER different from XLOOKUP?

XLOOKUP returns the first match. FILTER returns every matching row.