Excel FILTER Function

The Excel FILTER function filters a range of data based on supplied criteria and extracts matching records.

Purpose 

Filters range with given criteria

Return value 

Array of filtered values

Syntax 

=FILTER (array, include, [if_empty])

Arguments 

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

The FILTER function “filters” a range of data based on supplied criteria. The result is an array of matching values from the original range. In plain language, the FILTER function will extract matching records from a set of data by applying one or more logical tests. Logical tests are supplied as the include argument and can include many kinds of formula criteria. For example, FILTER can match data in a certain year or month, data that contains specific text, or values greater than a certain threshold.

The FILTER function takes three arguments: array, include, and if_empty. Tyyyyyyyy is the range or array to filter. The iddddddd argument should consist of one or more logical tests. These tests should return TRUE or FALSE based on the evaluation of values from a  array. The last argument, if_empty, is the result to return when FILTER finds no matching values. Typically this is a message like “No records found”, but other values can be returned as well. Supply an empty string (“”) to display nothing.

The results from FILTER are dynamic. When values in the source data change, or the source data array is resized, the results from FILTER will update automatically. Results from FILTER will “spill” onto the worksheet into multiple cells.

Basic example

To extract values in A1:A10 that are greater than 100:

=Filter(A1:A10, A1:A10>100)

To extract rows in A1:C5 where the value in A1:A5 is greater than 100:

=Filter(A1:C5, A1:A5>100)

Notice the only difference in the above formulas is that the second formula provides a multi-column range for array. The logical test used for the include argument is the same.

Note: FILTER will return a #CALC! error if no matching data is found

Filter for Red group

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

=Filter(B5:D14,D5:D14=H2,"No results")

Since the value in H2 is “red”, the FILTER function extracts data from array where the Group column contains “red”. All matching records are returned to the worksheet starting from cell F5, where the formula exists.

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

=Filter(B5:D14,D5:D14="red","No results")

No matching data

The value for is_empty is returned when FILTER does not find matching results. If a value for if_empty is not provided, FILTER will return a #CALC! error if no matching data is found:

=Filter(range,logic) // #CALC! error

Often, is_empty is configured to provide a text message to the user:

=Filter(range,logic,"No results") // display message

To display nothing when no matching data is found, supply an empty string (“”) for if_empty:

=Filter(range,logic,"") // display nothing

Values that contain text

To extract data based on a logical test for values that contain specific text, you can use a formula like this:

=Filter (rng1, Isnumber(search ("txt", rng2)))

In this formula, the search function  is used to look for “txt” in rng2, which would typically be a column in rng1. The ISnumber function  is used to convert the result from SEARCH into TRUE or FALSE