The FILTER function is one of the new dynamic array functions added to Excel in September 2018. You can find them in Excel Online and Microsoft 365.
Presentation of the FILTER function in Excel
Filtering your data is one of the most common tasks with Excel. And this task is very simple when you click on the little arrow in the header.
Now, with the dynamic array functions, it is possible to create a workbook where you can filter your data automatically in a function of the values of your criteria.
This function allows you to return several rows whereas the VLOOKUP function does not allow it.
FILTER function parameters
The FILTER function only needs 2 parameters to return a result.
- The column or columns to be returned filtered
- The filter (or the test)
- [Optional] The message when the result is empty
For example, we want to find information about the customer Peter.
- First of all, we select all the data of our document (here the name of the Table )
=FILTER (tbl_Sales,
- Select the column where is the value of your criterion
- Indicate the value of your criterion
=FILTER(Tbl_Sales,Tbl_Sales[Customers]="Peter")
All the rows where the customer name is Peter are returned by the function
Other examples with the FILTER function
The FILTER function accepts all types of logical tests. The FILTER function returns the rows when the result is TRUE.
- For example, to select all fruits except apples, we just have to write the following formula
= FILTER(Tbl_Sales; Tbl_Sales[Fruits]<>"Apple")
- You can also create a filter greater or lower than a value. Here, we select all the quantity greater than 20.
= FILTER(Tbl_Sales; Tbl_Sales[Quantity]> 20)
Result to display if there is no result
If the FILTER function doesn't return any row, you can customize the message to avoid an error message.
=FILTER(Tbl_Sales;Tbl_Sales[Customers]="John";"No Result")
Return any column, not necessarily the one with the criterion.
It is important to know that the columns to return can be different from the columns as the filter.
For instance, if we want to return only the fruits bought by Peter, we will write this simple formula.
=FILTER(Tbl_Sales[Fruits];Tbl_Sales[Customers]="Peter")
