Excel FILTER Function
Summary
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.
Usage notes
The Excel FILTER function "filters" a range of data based on supplied criteria. In the example show, 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".
More than one criteria
The include argument can be extended with boolean logic. For example, to extract only data where the group is "red" and score is greater than 80, you can use a formula like this:
=FILTER(B5:D14,(D5:D14="red")*(C5:C14>80),"No results")
Note: This function is a new "Dynamic Array Function" in Excel. It is a beta feature available only through the Office Insiders program. Dynamic Array functions are expected to be released in 2019 to Office 365 subscribers.
FILTER formula examples
To filter data to include data based on a "contains specific text" logic, you can use the FILTER function with help from the ISNUMBER function and SEARCH function. In the example shown, the formula in F5 is:
=FILTER(...
To filter data to include data based on dates, you can use the FILTER function with one of Excel's date functions. In the example shown, the formula in F5 is:
=FILTER(data,MONTH(date)=7,"No data")
where "...
To extract a list of unique values from a set of data, while applying one or more logical criteria, you can use the UNIQUE function together with the FILTER function. In the example shown, the formula in D5 is:
=...
To extract a list of unique values from a set of data, filtered by count or occurence, you can use UNIQUE with FILTER, and apply criteria with the COUNTIF function. In the example shown, the formula in D5 is:
=UNIQUE(...
To filter data to include records between two dates, you can use the FILTER function with boolean logic. In the example shown, the formula in F8 is:
=FILTER(B5:D15,(C5:C15>=F5)*(C5:C15<=G5),"No data...
To extract a list of unique values from a set of data, ignoring blank cells, you can use the UNIQUE function together with the FILTER function. In the example shown, the formula in D5 is:
=UNIQUE(FILTER(B5:B16,B5:B16...
To filter data to include only records where a value is this or that, you can use the FILTER function and simple boolean logic expressions. In the example shown, the formula in F5 is:
=FILTER(B5:D14,(D5:D14="red...
To filter data to include only records where a column is equal to one of many values, you can use the FILTER function together with the ISNUMBER function and MATCH function. In the example shown, the formula in F5 is...
Related functions
Good links
