Excel FILTER function

Description

The Excel FILTER function filters a range of data based on given criteria. Unlike the common lookup functions, FILTER returns all matching entries and full rows. The result is a dynamic array output.

Syntax

FILTER( array, include, [if_empty] )

Arguments

array – The range or array to filter

include – A boolean array used as filtering criteria

if_empty – [Optional] The message to display if no entry matches the include criteria

Return value

A dynamic array of filtered values

How to use the FILTER function in Excel

The Excel FILTER function filters a range of data based on criteria you supply. It returns a dynamic array that spills into the surrounding cells if the function is entered directly (not wrapped by another function).

FILTER is a tremendous addition to the set of existing lookup functions, as it offers a completely new approach to lookup values by returning multiple results and also multiple values for each match. FILTER is build with three arguments: The array that we want to be sort, a boolean array include that has a TRUE or FALSE value for each row in the array based on a criteria, and the if_empty message that is displayed if no entry matches the criteria.

The syntax of the FILTER function is very straight forward as soon as you understand how to construct the boolean array for the include argument. In its most simple form, FILTER returns exact matches by setting a lookup array equal to a value like in the following formula:

= FILTER(B6:D12, C6:C12=G3, “No result”)

In this example, we provide the range B6:D12 as the array to be filtered, and then for the include argument, we set the region column C6:C12 equal to our lookup value in G3 to construct a boolean array. As you see, with the lookup value “U” no result is found, because with this simple include condition only exact matches are displayed.

FILTER with Partial Match

To solve the problem that no result is displayed unless the lookup value is matched exactly, you have to adjust the include argument. Instead of setting the a column equal to a value, you can use the SEARCH function and wrap it inside the ISNUMBER function in order to include all rows for which the lookup value is contained in the value inside lookup column.

In the example show below, the formula is:

= FILTER(B6:D12,ISNUMBER(SEARCH(G3, C6:C12)), “No result”)

In this example, SEARCH returns the index of the search term (lookup value) for each Region value and returns an #N/A error if the search term is not contained in the region text. Thus, the ISNUMBER function only returns TRUE for all rows that contain a “U” in the Region column.

Another major improvement of this technique is the fact that the whole array is returned in case the search term is empty, while for the simple exact match “No result” will be returned for an empty search term.

FILTER with Partial Left Match

In case you want to return all rows in which the Region value begins with the search term, you have to use the LEFT function with the LEN function inside for the include argument.

In the example show below, the formula is:

= FILTER(B6:D12, LEFT(C6:C12,LEN(G3))=G3, “No result”)

In this example, the LEFT function returns the left part (with the same number of characters as the search term) of each Region value in the array and compares it to the search term. This way, the FILTER function returns all rows that begin the the search term “U”.

Just like for the Partial Match version, if the search term in G3 is empty, the whole array will be returned.

Multiple Criteria

The FILTER function is also capable of handling multiple filter criteria, either combined or separately. For this advanced topic, we recommend to take a look at our tutorial on Dynamic search boxes with the FILTER function.