Spreadsheet formulas explained, side by side

FILTER Function in Excel, Google Sheets, Airtable & Notion

FILTER returns all the rows of a range where a condition is TRUE, and the result spills into as many cells as needed. Unlike a lookup, it returns every match, not just the first.

Syntax in each app

AppSupportSyntax & notes
Excel✔ Built inFILTER(array, include, [if_empty])
Excel 2021 / Microsoft 365
Google Sheets✔ Built inFILTER(range, condition1, [condition2, ...])
No if_empty argument: Sheets returns #N/A when nothing matches, so wrap it in IFNA(). Extra conditions are combined with AND.
Airtable✘ Not available—
Use a filtered view to show matching records. To list matches on another record, use a rollup field over a linked record field with “Only include linked records that meet certain conditions”, aggregated with ARRAYJOIN(values, ", ").
Notion✔ Built inlist.filter(condition)
Works on lists and relations: prop("Students").filter(current.prop("Score") >= 80) returns the matching pages; add .map(current.prop("Name")) to get names.
Try FILTER with your own values
One item per line
One value per line, or separate with commas
Leave empty to see the no-match error
Result (as Excel & Google Sheets return it)
Ann, Cara, Eli Spills into 3 cells
Your formula, ready to paste
Excel=FILTER(A2:A6, B2:B6>=80, "None") open excel.new ↗
Google Sheets=IFNA(FILTER(A2:A6, B2:B6>=80), "None") open sheets.new ↗
Airtable✘ No equivalent. Use a filtered view to show matching records. To list matches on another record, use a rollup field over a linked record field with “Only include linked records that meet certain conditions”, aggregated with ARRAYJOIN(values, ", ").
Notionprop("Students").filter(current.prop("Score") >= 80).map(current.prop("Name"))

Excel & Sheets use cell ranges. Paste this sample data into cell A1 of a blank sheet, then paste the formula into any empty cell.

Name	Score
Ann	92
Ben	75
Cara	88
Dev	64
Eli	81

The Notion formula assumes a relation property Students to a database with Name and Score properties; it returns an empty list when nothing matches.

Arguments

ArgumentWhat it means
arrayThe range to return rows from, e.g. A2:B6.
includeA TRUE/FALSE test with one result per row, e.g. B2:B6>=80. Combine tests with * (AND) and + (OR).
[if_empty] optionalReturned when no rows match. Without it Excel shows #CALC!.

Examples

FormulaResultWhyCopy
=FILTER(A2:A6, B2:B6>=80)Ann, Cara, EliTester data: everyone scoring 80 or more.
=FILTER(A2:B6, B2:B6>=80)Ann | 92, Cara | 88, Eli | 81Return whole rows by filtering a wider range.
=FILTER(A2:A6, (B2:B6>=70)*(B2:B6<90))Ben, Cara, EliMultiply conditions for AND; add them for OR.
=FILTER(A2:A6, B2:B6>100, "None")Noneif_empty instead of #CALC!.

Examples use Excel / Google Sheets syntax unless noted.

How FILTER works

FILTER is the answer to “VLOOKUP only returns the first match”. Give it the range you want back and a condition with one TRUE/FALSE per row, and it returns every matching row, in the original order. The result is a dynamic array: it spills down (and across, for several columns) from the formula cell and resizes when the data changes. If any cell in the spill area is not empty you get #SPILL!; clear the cells below the formula.

The include range must have the same number of rows as the array, or you get #VALUE!. For several conditions, multiply them for AND, (B2:B6>=70)*(C2:C6="North"), and add them for OR. When nothing matches, Excel returns #CALC! unless you give the third argument. Google Sheets' FILTER has no if_empty argument and returns #N/A, so wrap it in IFNA; it accepts extra conditions as further arguments instead. Combine with SORT or UNIQUE to sort or de-duplicate the result.

FILTER needs Excel 2021 or Microsoft 365. In Airtable, filtering rows is what views are for; to show matching linked records inside another record, use a conditional rollup with ARRAYJOIN(values). Notion has a real filter() on lists and relations, using current for each item.

Frequently Asked Questions

Why does FILTER show #SPILL!?
Something is in the cells the result needs to spill into. Clear them, or move the formula.
How do I filter with two conditions?
Multiply them for AND: =FILTER(A2:C20, (B2:B20="North")*(C2:C20>100)). Add them for OR. In Google Sheets you can also pass each condition as a separate argument (AND).

Part of the Lookup & Reference functions. Results in the tester follow Excel and Google Sheets behaviour; always double-check important work in your own file.