FILTER Function in Excel and Google Sheets: Multiple Conditions, Errors and Examples

Updated 2026-11-03

Lookups like XLOOKUP and VLOOKUP return one match. Very often you want all of them: every order from one region, every task assigned to one person, every transaction in November. That is what FILTER does. You write it once in a single cell, and the results spill into the rows below, updating automatically when the data changes.

Who has FILTER?

• Excel: Microsoft 365, Excel 2021 and later, and Excel for the web. Excel 2019 and earlier do not have it.
• Google Sheets: available to everyone.

The basic syntax

Excel:          =FILTER(array, include, [if_empty])
Google Sheets:  =FILTER(range, condition1, [condition2, ...])

array / range is the data you want back, for example A2:D100. include / condition is a test that is TRUE for the rows to keep, the same height as the data, for example B2:B100="North". In Excel, the optional if_empty is what to show when nothing matches.

Imagine an orders table with Date in column A, Region in B, Product in C and Amount in D. To list every North order, type this in an empty cell with enough space below and to the right:

=FILTER(A2:D100,B2:B100="North")

Common recipes

The key difference: in Excel, multiple conditions go inside one argument, joined with * for AND and + for OR. Google Sheets also accepts that style, and additionally lets you list AND conditions as separate arguments.

Use a cell instead of typing the value

Put the region you want in G1 and refer to it, so anyone can change the filter without touching the formula:

=FILTER(A2:D100,B2:B100=G1,"No results")

Add a drop-down to G1 (Data Validation in Excel, Insert > Drop-down in Google Sheets) and you have a simple interactive report. Our drop-down list guide shows how to fill it from a list that grows automatically.

Return only some columns

To get just Product and Amount, filter those columns only:

=FILTER(C2:D100,B2:B100=G1)

If the columns you want are not next to each other, Excel 365 can wrap them with CHOOSECOLS: =CHOOSECOLS(FILTER(A2:D100,B2:B100=G1),3,1) returns Product, then Date.

Sort and de-duplicate the results

Sorted by Amount, largest first:  =SORT(FILTER(A2:D100,B2:B100=G1),4,-1)
Unique products in that region:   =UNIQUE(FILTER(C2:C100,B2:B100=G1))
Total of the filtered amounts:    =SUM(FILTER(D2:D100,B2:B100=G1))

For a single total, SUMIFS is simpler and works in older Excel too, but FILTER is handy when the condition is complicated.

Fixing FILTER errors

• #CALC! (Excel): nothing matched and you did not give an if_empty value. Add a third argument such as "No results".
• #N/A, "No matches are found in FILTER evaluation" (Google Sheets): same cause. Wrap the formula in IFNA, as in the table above.
• #SPILL! (Excel) or #REF!, "Array result was not expanded" (Google Sheets): something is in the way of the results. Clear the cells below and to the right of the formula. FILTER also cannot spill inside an Excel table, so put it in a normal range.
• #VALUE!: the condition range is a different size from the data range, for example A2:D100 with B2:B90. Make the row ranges match.
• Wrong or missing rows: check for extra spaces or numbers stored as text in the column you are testing. Our #N/A troubleshooting guide covers the same data problems for lookups.

Tips

• Use whole-column style ranges carefully. A2:D1000 is fine; full columns like A:D work but can slow big sheets.
• Point FILTER at an Excel table column (for example Orders[Region]) so new rows are included automatically.
• To pull matching rows into another sheet, write the formula on that sheet and refer back: =FILTER(Orders!A2:D100,Orders!B2:B100="North").
• FILTER results are live. If you need a frozen copy, copy the spilled range and paste as values.

← More guides