FILTER Function in Excel: Return Rows That Match
Use FILTER in Excel 365 and Google Sheets to pull matching rows into a new table: one condition, AND and OR conditions, and a message when nothing matches.
By Ayoub Farid · · 1 min read
FILTER returns every row that meets a condition, as a live table that updates when the data changes. It's like the filter button, but as a formula you can place anywhere.
Available in Microsoft 365, Excel 2021+, Excel for the web and Google Sheets.
Syntax
=FILTER(array, include, [if_empty])One condition
All Coffee rows from A2:D200 (category in C):
=FILTER(A2:D200,C2:C200="Coffee")With the category in a cell:
=FILTER(A2:D200,C2:C200=G1,"No matches")AND: multiply conditions
Coffee and sales over 100:
=FILTER(A2:D200,(C2:C200="Coffee")*(D2:D200>100))OR: add conditions
Coffee or Tea:
=FILTER(A2:D200,(C2:C200="Coffee")+(C2:C200="Tea"))Each condition returns TRUE/FALSE (1/0). Multiplying means both must be 1; adding means at least one.
Contains text
=FILTER(A2:D200,ISNUMBER(SEARCH("latte",A2:A200)))Sort the results
=SORT(FILTER(A2:D200,C2:C200="Coffee"),4,-1)Sorted by the 4th column, largest first. See SORT.
Only some columns
=FILTER(A2:A200,C2:C200="Coffee")Returns just the names.
Watch out for
#SPILL!: cells in the way.#CALC!: nothing matched and you didn't set if_empty.- The include range must be the same height as the array.
Frequently asked questions
How do I filter with a formula in Excel?
=FILTER(data,condition), e.g. =FILTER(A2:D200,C2:C200="Coffee").
How do I filter with multiple conditions?
Multiply conditions for AND, add them for OR, each in brackets.
What does #CALC! mean with FILTER?
Nothing matched. Add a third argument like "No matches".
Is FILTER available in Excel 2019?
No. It needs Microsoft 365 or Excel 2021 and later.