fx
FunctionPedia
Quick Answer

FILTER Function

FILTER pulls out matching rows from a table based on a condition. Unlike VLOOKUP (which returns one value), FILTER can return entire rows and multiple matches — all in one formula.

✓ Excel✓ Google SheetsExcel 365 / 2021+

Syntax

FILTER
(array, include, [if_empty])

Parameters

ParameterDescriptionRequired
arrayThe range or array to filter. Can be a single column or a multi-column table.Required
includeA Boolean array (TRUE/FALSE) of the same height as the array. TRUE rows are included, FALSE rows are excluded.Required
if_emptyThe value to return when no results match the filter. Default is #CALC!.Optional

Basic Example

Filter a product table to show only items in the 'Electronics' category

=FILTER(A2:D50, C2:C50="Electronics")
ResultShows all electronics rows (name, price, category, stock)

C2:C50="Electronics" creates a TRUE/FALSE array. FILTER keeps only rows where the category is 'Electronics' and spills the matching rows.

Advanced Examples

Example 1: Multiple condition filter (AND logic)

Data analysis

Filter for Electronics items priced under $100

=FILTER(A2:D50, (C2:C50="Electronics")*(B2:B50<100))
Result: Only electronics under $100
Multiply conditions with * for AND logic. TRUE×TRUE=1 (included), TRUE×FALSE=0 (excluded). Both must be TRUE.

Example 2: Multiple condition filter (OR logic)

Flexible search

Filter for items that are either 'Electronics' OR 'Books'

=FILTER(A2:D50, (C2:C50="Electronics")+(C2:C50="Books")>0)
Result: All electronics AND books
Add conditions with + for OR logic. TRUE+TRUE=2, TRUE+FALSE=1, FALSE+FALSE=0. >0 means at least one condition is met.

Example 3: FILTER with custom empty message

User-friendly output

Return 'No results found' when the filter produces no matches

=FILTER(A2:D50, C2:C50="Furniture", "No results found")
Result: No results found (if no furniture exists)
The third argument replaces the #CALC! error with your custom message when no rows match.

How FILTER Works

FILTER works by creating a Boolean mask over your data. The 'include' argument evaluates each row against your condition, producing an array of TRUE/FALSE values. Rows where the result is TRUE are kept; FALSE rows are dropped. The remaining rows spill into adjacent cells as a dynamic array. For multiple conditions, multiply (*) for AND logic or add (+) for OR logic — this converts TRUE/FALSE to 1/0, allowing math-based combination.

1
Select the data range
Choose the full table or column you want to filter (e.g., A2:D50).
2
Define the condition
Write a condition that produces TRUE/FALSE for each row: C2:C50="Electronics", B2:B50>100, etc.
3
Combine conditions if needed
Use * for AND: (cond1)*(cond2). Use + for OR: (cond1)+(cond2)>0.
4
Set empty result text
Add a third argument like "No results" for when no rows match.
5
Enter the formula
Type =FILTER(range, condition, empty_text) in a cell with enough space below for spilled results.

Important Notes & Limitations

  • FILTER is only available in Excel 365 and Excel 2021+ — not in earlier versions.

  • Results spill into adjacent cells — those cells must be empty or you'll get a #SPILL! error.

  • FILTER returns all matching rows — there's no built-in way to limit to top N results (use SORT + INDEX for that).

  • Large datasets may slow down recalculation since FILTER re-evaluates on every change.

Common Errors & Fixes

#CALC!No rows match the filter condition and no if_empty argument was provided

Fix: Add a third argument: =FILTER(range, condition, "No results") to display a custom message instead of the error.

#SPILL!Filtered results need to spill into cells that already contain data

Fix: Clear the cells below and to the right of the FILTER formula to give it room to spill results.

#VALUE!The include array has a different number of rows than the data array

Fix: Ensure both arrays have the same height. If data is A2:D50 (49 rows), include must also span 49 rows.

Download Practice File

Practice FILTER with Real Data

Download a sample CSV file with pre-populated data and practice exercises for the FILTER function. Works in both Excel and Google Sheets.

Works in Google SheetsCompatible with ExcelIncludes exercises

File format: CSV (comma-separated values) - opens in Excel, Google Sheets, and all spreadsheet apps

Related Tutorials

Master Excel Dynamic Arrays (Excel 365)

Learn how to use modern dynamic array functions like FILTER, SORT, UNIQUE, and SEQUENCE to transform your spreadsheets.

Google Sheets ARRAYFORMULA

Google Sheets ARRAYFORMULA applies one formula to an entire range at once, so you never copy down again. Learn how to combine it with IF, FILTER, and QUERY.

Frequently Asked Questions

How do I filter with multiple AND conditions?
Multiply conditions: =FILTER(A2:D50, (C2:C50="Electronics")*(B2:B50<100)). Both must be TRUE for a row to be included.
How do I filter with multiple OR conditions?
Add conditions and check >0: =FILTER(A2:D50, (C2:C50="Electronics")+(C2:C50="Books")>0). At least one must be TRUE.
Can FILTER return just one column?
Yes. Set the array to a single column: =FILTER(B2:B50, C2:C50="Electronics") returns only the prices of electronics.
Does FILTER work in Excel 2019?
No. FILTER requires Excel 365 or Excel 2021+. In older versions, use INDEX/MATCH or VLOOKUP for similar (but less powerful) functionality.
How do I sort filtered results?
Wrap FILTER in SORT: =SORT(FILTER(A2:D50, C2:C50="Electronics"), 2, -1) filters electronics and sorts by price descending.