Syntax
=FILTER(array, include, [if_empty])Arguments
| Argument | Required | Description |
|---|---|---|
array | Required | The range or array to filter |
include | Required | TRUE/FALSE values, one per row (or column) |
if_empty | Optional | What to return when nothing matches |
How to use FILTER
When FILTER returns more than one value, the result spills into the cells below and to the right. Keep those cells empty, or you get #SPILL!.
FILTER takes 2 required arguments and 1 optional argument. Type =FILTER( in a cell and SheetsBat shows the arguments as you go. Optional arguments are shown in square brackets; leave them out to use their default.
Examples
Each result below was calculated by the SheetsBat formula engine.
=FILTER({1;2;3;4}, {1;2;3;4}>2)Result Spilled result
| A | |
|---|---|
| 1 | 3 |
| 2 | 4 |
=FILTER({1;2}, {FALSE;FALSE}, "none")Result =FILTER({1;2}, {FALSE;FALSE})Result Related array functions
- CHOOSECOLSReturns the given columns of an array (negative numbers count from the end).
- CHOOSEROWSReturns the given rows of an array (negative numbers count from the end).
- DROPRemoves a number of rows or columns from the start (positive) or end (negative) of an array.
- EXPANDExpands an array to the given number of rows and columns, filling new cells with a pad value.
- FLATTENPuts the values of one or more ranges into a single column, reading row by row.
- GROUPBYGroups rows by the values of one or more columns and aggregates the values of each group.
- HSTACKPlaces arrays side by side; shorter columns are padded with #N/A.
- MAKEARRAYBuilds an array of the given size by calling a LAMBDA with each row and column number.
- MAPApplies a LAMBDA to each value of one or more arrays and returns the results.