FILTER

Formulas / FILTER
Filters a range of cells based on supplied criteria

=FILTER(array,include,[if_empty])

array - the array to filter
include - a Boolean array with the same height or width of the array
if_empty - [OPTIONAL] the value to return if all values in the included array are empty

Examples

=FILTER(A1:A10,A1:A10>100)
The formula returns all values in A1:A10 that are greater than 100:
=FILTER(A1:C5,A1:A5>100)
The formula returns every row in A1:C5 that has a value in A1:A5 that is greater than 100:
=FILTER(B5:D14,D5:D14=H2,"No results")
The formula returns all values in B5:D14 that are "red" when the value in H2 is "red":
=FILTER(B5:D14,D5:D14="red","No results")
The formula returns all values in B5:D14 that are "red":

The FILTER function is used to filter an array based on the criteria entered, and returns an array with the matching results.

The FILTER function uses criteria to filter a range of data and extract matching records using logical tests.
The FILTER function returns an array of filtered values.
If the FILTER function does not find matching data, it throws a #CALC! error.
The FILTER function works with vertical and horizontal arrays.
If the include argument has incompatible dimensions with the array argument, the FILTER function throws a #VALUE! error.
An error is thrown if the include array has errors.

What is the FILTER function?

The FILTER function is a Sourcetable function that filters data using criteria.

When did the FILTER function become available?

The FILTER function became available in Office 365.

Does the FILTER function automatically update when values in the source data change?

Yes, the FILTER function automatically updates when values in the source data change.

Can the FILTER function use wildcard characters?

No, the FILTER function cannot use wildcard characters.