FILTER Function in Excel: Return Every Row That Matches

📎 This article includes 1 downloadable practice file ↓

Dynamic ArrayLevel: ExpertAvailable in: Excel 365 / 2021+

In this article

1. Syntax
2. Examples
3. Example 1
4. Example 2
5. Example 3
6. Power combo
7. Common errors and fixes
8. FILTER in practice
9. Where you will use it
10. Worked example
11. Mistakes people make
12. Related functions
13. Continue learning

FILTER returns all rows (or columns) that meet a condition, and the result updates live as data changes.

Syntax

=FILTER(array, include, [if_empty])

Argument	What it means
array	The data to filter.
include	TRUE/FALSE test with the same height, e.g. B2:B500=”North”.
if_empty	What to show when nothing matches.

Examples

Example 1

=FILTER(A2:E500, B2:B500="North", "None")

All North rows.

Example 2

=FILTER(A2:E500, (B2:B500="North")*(E2:E500>50000))

AND: multiply conditions.

Example 3

=FILTER(A2:E500, (B2:B500="North")+(B2:B500="East"))

OR: add conditions.

Power combo

=SORT(FILTER(A2:E500, E2:E500>50000), 5, -1)

Live “big deals” report sorted by amount.

Common errors and fixes

You see	Why, and the fix
#SPILL!	Something is in the way of the results — clear the cells below/right.
#CALC!	Nothing matched and if_empty is missing.

FILTER in practice

Where you will use it

• All orders of one customer
• Overdue invoices only
• Live lists that update as data changes

Worked example

Customers in the region, joined into one cell.

Region	Formula	Result
North	=IFERROR(TEXTJOIN(", ",,FILTER({"Patel";"Shah";"Mehta";"Iyer"},{"North";"South";"North";"South"}=A2)),"none")	Patel, Mehta
South	=IFERROR(TEXTJOIN(", ",,FILTER({"Patel";"Shah";"Mehta";"Iyer"},{"North";"South";"North";"South"}=A3)),"none")	Shah, Iyer
East	=IFERROR(TEXTJOIN(", ",,FILTER({"Patel";"Shah";"Mehta";"Iyer"},{"North";"South";"North";"South"}=A4)),"none")	none

Results calculated in Excel.

Mistakes people make

Mistake	What to do instead
No matches	#CALC!; use the if_empty argument
Spill blocked	Clear cells below the formula

Related functions

SORT · UNIQUE · XLOOKUP · TEXTJOIN

📚 Part of the free Excel course: Beginner → Expert · Try it in the Formula Lab or ask the AI Helper.

Continue learning

All Lookup & Reference guides →

Excel #N/A Error in VLOOKUP and XLOOKUP: 5 Fixes That Actually WorkThe #N/A ('Value Not Available') error in Microsoft Excel signals that a lookup function—such…TOROW Function in Excel: Flatten Into One RowTOROW flattens a block into a single row, optionally skipping blanks and errors.WRAPCOLS Function in Excel: Fold a List Into ColumnsWRAPCOLS takes a long list and wraps it into columns of a fixed height…WRAPROWS Function in Excel: Reshape a List Into a GridWRAPROWS reshapes a single column into rows of a fixed width — e.g. a…

📎 Practice files for this article

• 📗
  Practice workbook: Dynamic arrays18 tasks on realistic sales data — type your formula, the Check column turns green when it is right. Answers sheet included.
  ⬇ XLSX · 35 KB

Free to use for learning. Files with macros (.bas) are plain text — import them with Alt+F11 → File → Import File, and always test on a copy.