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.
FILTER( array, include, [if_empty] )
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
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:
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:
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 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.
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.
The Excel XLOOKUP function searches an array, and returns an item corresponding to the first match it finds. It replaces many older lookup functions.
The Excel UNIQUE function returns a list of unique values in a given range. The result is returned as a dynamic array.
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.
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.
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.