ID EN
…
#Dynamic array

FILTER

Excel Functions

Filter range with given criteria

Syntax

EXCEL 
=FILTER(array, include, [if_empty])

Arguments

Parameter Description
array Range or array to filter.
include Boolean array, supplied as criteria.
if_empty [optional] Value to return when no results are returned.

Return Value

Array of filtered values

Details

The FILTER function "filters" data based on one or more conditions, and extracts matching values. The conditions are provided as logical expressions that test the source data and return TRUE or FALSE. The result from FILTER is an array of matching values from the original data. The results from FILTER are dynamic. If source data changes or if conditions are modified, FILTER will return new results. This makes FILTER a very good way to isolate and inspect specific data without altering the original dataset. Watch the video below to see a basic example of FILTER in action: FILTER is a flexible function that can extract matching data based on a wide variety of conditions. If you can apply a test that returns TRUE or FALSE, you can use that test to extract data with FILTER. You can filter data

Examples

Basic example

The formula in cell D5 looks like this:

EXCEL 
=FILTER(B5:B14,B5:B14>100)
Basic example

Notice that the include argument is a logical expression, B5:B14. Because there are 10 cells in the range, the expression returns an array that contai

EXCEL 
=FILTER(B5:B14,{FALSE;TRUE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE})
Filter for Red group

In the example shown above, the formula in F5 is:

EXCEL 
=FILTER(B5:D14,D5:D14=H2,"No results")
Filter for Red group

Values can be hardcoded as well. The formula below has the same result as above with "red" hardcoded into the criteria:

EXCEL 
=FILTER(B5:D14,D5:D14="Red","No results")
No matching data

FILTER will return a #CALC! error if no matching data is found, but you can use the optional is_empty argument to return a different result. Often, is

EXCEL 
=FILTER(array,include,"No results") // display message
No matching data

To display nothing when no matching data is found, provide an empty string ("") for if_empty:

EXCEL 
=FILTER(array,include,"") // display nothing

See Also

UNIQUE FILTER SORT SORTBY RANDARRAY SEQUENCE