Lookup & Reference Modern

FILTER

Returns the rows (or columns) of a range that meet a condition.

Syntax

FILTER(array, include, [if_empty])

What it returns

A spilled array of the matching rows (or columns).

Arguments

ArgumentRequiredDescription
array Yes The range to filter.
include Yes A boolean array — the condition that selects rows (or columns).
if_empty No The value to return if nothing matches.

Example

Source
ABC
1RegionSales
2North1200
3South800
4East1500
5West600
6
Result
ABC
1RegionSales
2North1200
3East1500
4
=FILTER(A2:B5, B2:B5>1000)

Important to know

Spills into multiple cells — needs empty space below/right.

Returns #CALC! if nothing matches and if_empty is omitted.

365 / 2021+ only (#NAME? in older versions).

Related functions

SORT UNIQUE

Related learning

No MEE lesson covers this function yet.