FILTER

Dynamic arraysMicrosoft 365

Return only the rows of a range that meet a condition — results spill.

Syntax

FILTER(array, include, [if_empty])

Arguments

array: The data to filter (can be several columns wide).
include: A boolean array the same height as `array`, e.g. (Region="North").
if_emptyoptional: What to show when nothing matches, e.g. "No results".

Examples

=FILTER(Data, Region="North", "None")

→

=FILTER(Data, (Region=A2)*(Amount>1000))

→ AND — multiply conditions

=FILTER(Data, (Region="North")+(Region="South"))

→ OR — add conditions

Good to know

Combine with SORT and UNIQUE: =SORT(UNIQUE(FILTER(...))).

Related functions

SORT UNIQUE XLOOKUP SORTBY

More Dynamic arrays functions

BYCOL BYROW CHOOSECOLS CHOOSEROWS DROP EXPAND GROUPBY HSTACK MAKEARRAY MAP PIVOTBY RANDARRAY

← All 510 functions

Syntax follows Microsoft’s documentation. Examples are written to be pasted into a cell and checked. Argument separators may be ; in your locale. The AI explainer runs on a free community model — verify anything important.