How do I filter with multiple conditions?

Multiply conditions for AND: (A2:A7="East")*(C2:C7>500). Add them for OR.

Can FILTER return several columns?

Yes. Pass a multi-column array such as A2:C7 and the result spills into all columns.

Does FILTER update automatically?

Yes. It recalculates when the source data changes.

What is the difference between FILTER and the Filter button?

The button hides rows in place. FILTER creates a separate, formula-driven list and leaves the source untouched.

← All functions

Dynamic arrays

FILTER

Rows that meet a condition.

Syntax

FILTER(array, include, [if_empty])

Example: =FILTER(A2:C100,B2:B100="Kitchen")

Available in: Excel 2021+, Microsoft 365

FILTER returns only the rows (or columns) of a range that meet a condition, and the results spill into the cells below: =FILTER(array, include, [if_empty]). It replaces manual filtering and many INDEX/SMALL array formulas in Microsoft 365 and Excel 2021 or later.

When to use it

You want a live list of rows that match a rule, such as all East orders or all invoices over $1,000.
You need several matches from a lookup, where VLOOKUP returns only the first.
You feed a clean subset into another function, such as SUM, TEXTJOIN or SORT.
You build dashboards that update automatically as data changes.

Arguments explained

array. The range or array to return rows (or columns) from. The result spills into the cells below and to the right.
include. A TRUE/FALSE array with the same height as array, for example (B2:B20>100). Combine conditions with * for AND and + for OR.
if_empty. Optional. What to show when nothing passes the test, such as "No results". Without it the formula returns #CALC!.
Spill area. Leave the cells below the formula empty, otherwise the result is #SPILL!.

Worked examples

Amounts for one region

	A	B	C
1	Region	Rep	Amount
2	East	Ana	1200
3	West	Ben	850
4	East	Cara	430
5	North	Dan	970
6	West	Eli	1500
7	East	Ana	610

Formula: E2: =FILTER(C2:C7,A2:A7="East")

Result: Spills down: 1200, 430, 610

The include test A2:A7="East" is TRUE for three rows, so three amounts spill down from E2.

Names with a large amount

	A	B	C
1	Region	Rep	Amount
2	East	Ana	1200
3	West	Ben	850
4	East	Cara	430
5	North	Dan	970
6	West	Eli	1500
7	East	Ana	610

Formula: E2: =FILTER(B2:B7,C2:C7>900)

Result: Spills down: Ana, Dan, Eli

Returns the Rep for every row where Amount exceeds 900.

Friendly message when nothing matches

	A	B	C
1	Region	Rep	Amount
2	East	Ana	1200
3	West	Ben	850
4	East	Cara	430
5	North	Dan	970
6	West	Eli	1500
7	East	Ana	610

Formula: E2: =FILTER(A2:A7,C2:C7>5000,"None")

Result: None

The third argument replaces the #CALC! error with your text.

Try it

A tiny spreadsheet with example data. Edit the formula or any number and see the result.

Formula in F2: =FILTER(B2:D7,A2:A7="East")

Example spreadsheet for FILTER
	A	B	C	D	E	F	G	H
1	Region	Product	Units	Price		Result
2	East	Pen	120	2.5		Pen	120	2.5
3	West	Pen	80	2.5		Book	45	12
4	East	Book	45	12		Lamp	15	25
5	North	Book	60	12
6	West	Lamp	30	25
7	East	Lamp	15	25

Result in F2: Pen

Practice this in a lesson: FILTER

Common errors and how to fix them

#SPILL!. Something is in the way of the result range. Clear the cells below and to the right of the formula.
#CALC!. No row met the condition and you gave no if_empty text. Add the third argument.
#VALUE!. The include array is not the same height as the array being filtered.
#NAME?. Excel 2019 and older do not have FILTER.

Related functions

Frequently asked questions

How do I filter with multiple conditions?: Multiply conditions for AND: (A2:A7="East")*(C2:C7>500). Add them for OR.
Can FILTER return several columns?: Yes. Pass a multi-column array such as A2:C7 and the result spills into all columns.
Does FILTER update automatically?: Yes. It recalculates when the source data changes.
What is the difference between FILTER and the Filter button?: The button hides rows in place. FILTER creates a separate, formula-driven list and leaves the source untouched.