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
| App | Support | Syntax & notes |
|---|---|---|
| Excel | ✔ Built in | FILTER(array, include, [if_empty])Excel 2021 / Microsoft 365 |
| Google Sheets | ✔ Built in | FILTER(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 in | list.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. |
| 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, ", "). | |
| Notion | prop("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
| Argument | What it means |
|---|---|
array | The range to return rows from, e.g. A2:B6. |
include | A TRUE/FALSE test with one result per row, e.g. B2:B6>=80. Combine tests with * (AND) and + (OR). |
[if_empty] optional | Returned when no rows match. Without it Excel shows #CALC!. |
Examples
| Formula | Result | Why | Copy |
|---|---|---|---|
=FILTER(A2:A6, B2:B6>=80) | Ann, Cara, Eli | Tester data: everyone scoring 80 or more. | |
=FILTER(A2:B6, B2:B6>=80) | Ann | 92, Cara | 88, Eli | 81 | Return whole rows by filtering a wider range. | |
=FILTER(A2:A6, (B2:B6>=70)*(B2:B6<90)) | Ben, Cara, Eli | Multiply conditions for AND; add them for OR. | |
=FILTER(A2:A6, B2:B6>100, "None") | None | if_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).