Dynamic arrays Updated November 2026

FILTER

FILTER(array, include, [if_empty]) returns every row of a range for which the include condition is TRUE, spilled into the cells below and to the right. Multiply conditions for AND and add them for OR. It needs Excel 2021 or Microsoft 365.

Syntax

FILTER(array, include, [if_empty])

Part	What it is
`array`	The range to return rows from.
`include`	A TRUE/FALSE array with the same number of rows, such as C2:C100 = "North".
`if_empty`	What to return when nothing matches. Without it you get #CALC!.

Examples

=FILTER(A2:D100, C2:C100 = "North", "No rows")

Result: All columns of every row where C is North, or No rows

=FILTER(A2:D100, (C2:C100 = "North") * (D2:D100 > 500))

Result: Rows where C is North and D is above 500

Multiplying the conditions gives AND.

=FILTER(A2:D100, (C2:C100 = "North") + (C2:C100 = "South"))

Result: Rows where C is North or South

Adding the conditions gives OR.

=SORT(FILTER(A2:D100, D2:D100 > 500), 4, -1)

Result: The matching rows sorted by the fourth column, largest first

Common mistakes

#CALC!: no row matched and there is no if_empty value. Add one.
#VALUE!: the include array has a different number of rows than array (A2:D100 with C2:C90).
#SPILL!: the output area is not empty. Clear the cells below and to the right of the formula.
Using AND(...) or OR(...) in include returns a single value, not an array; use * and + instead.

Practice it

The drills on Formuladrill check your answer on live data, free, no account needed.

Start practicing

Questions

How do I filter with multiple criteria?

Multiply the conditions for AND: (A2:A100 = "X") * (B2:B100 > 10). Add them for OR: (A2:A100 = "X") + (A2:A100 = "Y").

How do I return only certain columns?

Give FILTER a smaller array, such as B2:C100 instead of A2:D100, or wrap it in CHOOSECOLS in Microsoft 365: =CHOOSECOLS(FILTER(A2:D100, C2:C100 = "North"), 1, 4).

How do I filter for text that contains a word?

=FILTER(A2:D100, ISNUMBER(SEARCH("urgent", B2:B100))).

SORT and SORTBYHow the Excel SORT and SORTBY functions sort a range with a formula that updates itself, with examples for descending order, several columns and outside keys.
UNIQUEHow the Excel UNIQUE function returns a list without duplicates that spills down the sheet, with examples for a distinct list, a distinct count and a sort.
XLOOKUPHow XLOOKUP works in Excel: find a value in one column and return the match from another, in any direction, with a not found value and next-smaller match.
SUMIFSHow to use SUMIFS in Excel to add values that meet two or more conditions, with examples for region and status, date ranges, not-equal and wildcards.