Excel FILTER function

How to use the 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”)

Excel FILTER function - Exact Match

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”)

Excel FILTER function - Partial Match

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”)

Excel FILTER function - Partial Left Match

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.

Formula examples

Related Tutorials

Related Functions

How to use the Excel VLOOKUP function

Excel VLOOKUP function

The Excel VLOOKUP function searches for a value by matching on the first table column and returns the corresponding value from a column in the same row.

How to use the Excel SORTBY function

Excel SORTBY function

The Excel SORTBY function sorts the contents of a given range or array based on the values of another range or array. It returns a dynamic array result.

How to use the Excel UNIQUE function

Excel UNIQUE function

The Excel UNIQUE function returns a list of unique values in a given range. The result is returned as a dynamic array. 

How to use the Excel SORT function

Excel SORT function

The Excel SORT function sorts the contents of a given range or array and returns a dynamic array result. Values can be sorted by one more multiple columns.

How to use the Excel XLOOKUP function

Excel XLOOKUP function

The Excel XLOOKUP function searches an array, and returns an item corresponding to the first match it finds. It replaces many older lookup functions.

Facebook
Twitter
LinkedIn
Reddit
WhatsApp
Email