EXCEL function
FILTER
for Excel 2007 to 2019
Download the Excel file with the VBA code of the functions
SORT, SORTBY, FILTER, XLOOKUP, XMATCH, UNIQUE, SEQUENCE, RANDARRAY
and all examples shown on these web pages:
▸Download Excel file
(Version 3.7 - Last update: 2023-01-06 - XLookup and XMatch now fit for use in array formulas ▸more on this)
What is it about?
In Excel 365 and from Excel 2021, Microsoft implemented the long-missing cell functions SORT, SORTBY, FILTER, XLOOKUP and others. However, they were not retrofitted for older versions of Excel, so that all users from Excel 2007 up to and including Excel 2019 had to do without them.
There is now a solution for users of older versions of Excel (since the end of January 2022).
With the help of VBA so-called UDFs (user defined functions) were implemented that have exactly the same parameter lists as the corresponding Microsoft functions.
In cases where the functions provided here return multiple values, their input must be terminated with CTRL-SHIFT-ENTER. Before entering the formula, an area must be selected on the spreadsheet that contains the returned data - as is usual with the so-called CSE array formulas (CSE = Control Shift Enter).
▸This tutorial provides a basic and detailed introduction to array formulas.
With Function names of UDFs it doesn't matter whether the letters are written in upper or lower case.
For instructions on integrating the functions in your own Excel file, see ▸Integrating the functions.
FILTER function
If you are looking for a way to filter with wildcards, you can jump straight down to the ▸relevant chapter.
The Filter_ function is a replacement for the FILTER function which is only available in Excel 365. It expects exactly the same parameter list as the Excel 365 function and has the same filtering behavior.
The function name is "Filter_" with an underscore at the end because Excel does not accept the function name "Filter" as a name for a UDF.
= Filter_ ( array, filter criteria, [if_empty] )
| parameters | explanation |
|---|---|
| array | range or array to filter |
| filter criteria | An expression that produces a Boolean array whose length equals the height or width of the array to be filtered |
| if_empty (optional) |
A string that appears when the list of filtered dates is empty default: if_empty = "" |
See also the function description from Microsoft:
▸https://support.microsoft.com/en-us/office/filter-function-f4f7cb66-82eb-4767-8f7c-4877ad80c759
Example: = Filter_ ($B$3:$F$85, $F$3:$F$85>1000, "The filtered list is empty.")
In this example, the column F is used for filtering. From the range $B$3:$F$85, those rows are filtered out in with the value in column F is greater than 1000.
The function uses the (vertical) filter criterion $F$3:$F$85>1000 to recognize that vertical filtering is desired. This means that certain rows are filtered out.
Four more examples follow for illustration, which also show the combination of the functions Sort_ and Filter_ as well as the nesting of the Filter_ function (multiple filtering).
Filter - example 1
In this example horizontally is filtered. So certain columns are filtered out.
The filter criterion is: (B5:E5="Region") + (B5:E5="Sales")
The columns 'Region' and 'Sales' are filtered out here.
RULE:
If several filter criteria are linked, they must be enclosed in brackets.
For the OR concatenation, the plus sign (+) is used, for the AND concatenation the multiplication operator (*) is used.
Since the dynamic adjustment of the output area cannot be simulated with VBA, you have to select a specific output area on the worksheet before entering the formula, as is usual with CSE array formulas. It is best to choose the same size as that of the original data.
Filter - example 2
In example 2 filtering is vertical, i.e. certain rows are filtered out.
In this case, all those rows are filtered out in which column E has sales between 1000 and 1500.
Filter - example 3
Example 3 shows how to combine the Sort_ and Filter_ functions. The filtered data from example 2 is also sorted by sales. To do this, you pass the complete filter expression of the previous formula as the first parameter to the Sort_ function.
Filter - example 4
Finally, an example for multiple filtering.
It is first filtered horizontally. The columns 'Region' and 'Sales' are filtered out. The second is vertical filtering. All sales greater than 1000 are filtered out.
The inner filter function of the formula filters the columns 'Region' and 'Sales' out. This whole inner filter expression is used in the outer filter function as the first parameter. The outer function takes over the result array of the inner function and filters it according to the column 'Sales'.
Filtering with wildcards
Unfortunately, Microsofts FILTER function does not support the use of wildcards (*, ?, ~) in the filter criteria.
However, if you are looking for a simple function for filtering with wildcards, you will find a solution in the XLOOKUP2 function. This function is not a built-in function of Excel, but a UDF (User Defined Function) programmed with VBA.
A small example file is intended to demonstrate the use of this function XLookup2 for filtering with wildcards:
▸Download the sample file for filtering with wildcards
In addition to the "XLookup2" function, the example file also contains the "Sort_", "Filter_", "XLookup" etc. functions for older versions of Excel mentioned at the top of this web page.
The formula used is:
=XLookup2($G$2,$B$6:$B$25,$B$6:$E$25,"",2,2)
The first parameter $G$2 is the search term. It can contain the wildcards * ? ~.
The second parameter $B$6:$B$25 is the column to search in.
The third parameter $B$6:$E$25 specifies the range from which the hits are returned.
A string can be specified in the fourth parameter, which will be displayed if the search is unsuccessful.
The fifth parameter "2" indicates that a search with wildcards takes place.
The sixth parameter "2" means to search in normal search order (first to last element) and return all matches, not just the first.
Hint:
Since this is a so-called array formula, the range G6:J25 must first be selected before entering the formula in the editing line. The entry of the formula must be completed using CTRL-SHIFT-ENTER.
Further information about the XLookup2 function and a more complex example for filtering with wildcards can be found here:
▸Example 4 - filtering with wildcards
Sharing knowledge is the future of mankind