Description

Excel Filter function is used to return multiple results matching a search criteria. It is available in Microsoft 365 current channel subscribers only. If you use Office 2019 or older versions to open a workbook built with FILTER() or other dynamic array formulas, it will not work.

Excel Filter Function Syntax

=FILTER(array, include [if_empty])

array – the range to include in result

include – criteria. That is, what to include.

if_empty – what to do if FILTER returns an empty array. Normally set to double quotation marks “”. According to Microsoft, “If your dataset has the potential of returning an empty value, then use the 3rd argument ([if_empty]). Otherwise, a #CALC! error will result, as Excel does not currently support empty arrays.”

About the Data

The examples below come from a fictional membership database of a magazine company. Some members do not provide their full names, and so you might see people on a first-name basis in Full Name column.

Let’s begin Excel FILTER() function guide with a simple example, then we will move towards more complex cases and debugging.

Example with Single Criteria

In the example below, the user put in a name in cell H2, to populate fields below the range G4:J4. Since there are two names matching the input, two fields are returned.

Filtering with single criteria in Excel 365

If you simply want to return Email column without the names, you can change the array reference in first argument to $E$1:$E$63 instead. Same goes for if you want to return phone and email, then you use $D$1:$E$63 as array argument.

The thing to take note of is that you cannot ask FILTER() function to return columns that are not next to each other. Also, you cannot rearrange the column order. We will see how to do these further down the post.

Excel Filter Function Wildcard

Excel FILTER() function does not allow wildcards, “?” or “*” symbols.

To find near matches, you can use ISNUMBER(SEARCH(find_text, within_text)) in Include argument instead. In which case, you do not need to include wildcard characters.

If you need to match cases, replace SEARCH() function with FIND() function.

In the screenshot, you can see four coloured cells that should be the right matches.

The formula in cell F5 is

=FILTER($A$2:$D$24,ISNUMBER(SEARCH($G$2,$B$2:$B$24)),””)

Excel Filter Function Multiple Criteria

If you have criteria that applies to multiple columns, you can extend the principles described below to build your formula.

AND Logic

If you only want to return records where two or more criteria need to be true at the same time, you are applying AND logic.

To do this, enclose each criteria in a round bracket, and multiply them using asterisk (*) symbol.

In the example, we are looking for where Full Name is GARCIA and Subscription Date is in the year 2021.

The formula in cell G14 is

=FILTER($B$2:$E$63,($B$2:$B$63=$H$10)*(YEAR($A$2:$A$63)=2021),””)

Here we are using Year() function on the range in column A to return year from dates.

OR Logic

If you want to return records that match any one of the specified criteria, you are applying OR logic in Excel FILTER() function.

To do this, enclose each criteria in a round bracket, and add them up using plus(*) symbol.

Supposed we want to search records where Full Name can be either MARTIN or GARCIA.

The formula in cell G22 is

=FILTER($B$2:$E$63,($B$2:$B$63=$H$18)+($B$2:$B$63=$H$19),””)

Note that the use of dollar ($) sign is not necessary since you do not need to drag the formula across a range. It is however, a good habit to stick to.

Excel Filter Function on Table Range

It is recommend that data be converted to a table (Ctrl+T keyboard shortcut). The advantages being

  • You do not have to update the range in formula
  • Easier to select the range since you do not need to know position of the last row (This only applies if you are not selecting the whole column in the first place)
  • Ranges in formula are referred to by their column names, giving them meaning.

In the downloadable workbook, range addresses in Data worksheet are replaced by their table counterparts. This is done easily by replacing the range, then use the mouse to select table columns.

Excel filter function on tables

An example with single criteria in cell G5 is

=FILTER(tblData[[Full Name]:[Email]],tblData[Full Name]=$H$2)

where

tblData[[Full Name]:[Email]] specifies the columns to return, namely Full Name column to Email column inclusive.

Returning Non-Adjacent Columns

This trick is from Leila Gharani. You can find the Youtube link here.

In the downloadable Excel Filter Function workbook, you can find it in “Incongruous Column” worksheet

Essentially you apply another FILTER() to your original FILTER() function to take out columns you do not want to be included. This is done by array constants that signal to Excel what to include. Any value greater than 0 means to include, 0 means to exclude.

The formula in cell F5 is

=FILTER(FILTER($B$2:$D$24,$C$2:$C$24=$G$2,””),{1,0,1},””)

Here, array range is between columns B and D. However, since we tell Excel to ignore the middle column (column C), it will only return columns B and D.

You can include as many columns as you like in array range, but then you will need to also expand the number of constants between the curly brackets. So say you referenced five columns, and you want to show the first, second and last only, then you should have {1,1,0,0,1} in your Include argument of that outward FILTER().

Returning Columns in Different Orders

Excel Filter Function returning columns in different orders

In Reverse Column worksheet, two methods are given.

The first one is easiest to get, although it involves dragging the formula across. The example given returns two columns. You can however, apply it to multiple columns and extract any column in any order.

Here is a formula as an example:

=INDEX(FILTER($A$2:$B$24,$B$2:$B$24=$G$2,””),,2)

This will return the second column, column B, from the filter range.

This is faster than specifying single column in array argument (first argument), and having to change range reference when formula is applied to other columns.

The second method uses the classic IF({1,0}) array trick to return two incongruous ranges.

For example, the formula in cell F12 is

=FILTER(IF({1,0},$B$2:$B$24,$A$2:$A$24),$B$2:$B$24=$G$2,””)

This swaps the position of columns A and B in the result returned.

Excel Filter Function Not Working

If your FILTER() function is returning #CALC error, it could be that you included cell ranges in Include argument that do not return the same data type as you expected. For example, you may have included header cell in a column of dates.

Alternatively, if you don’t specify the third argument, if_empty, and FILTER() returns an empty array, it will give #CALC error.

#N/A or #Value

If you have error values in ranges referred to in the second argument, it can result in #N/A or #Value error.

Also, if the dimension of your Include array is not the same size as one of your data array columns in the first argument, it will return #Value! error.

#SPILL

In most use case, FILTER() function returns an array that cover multiple rows, with a width equal to what you specified in the first argument as the column range.

If there are cells with values located in the path of the array range, then instead of overwriting those cells, Excel will tell you that something is in the way. Clear up these cells first.

Excel Filter Function Not Found

Excel filter function is not available in Excel 2016 and previous versions. So do not be surprised if you think that the filter function is missing from Excel. You can however, use free Microsoft Excel Online or Google Sheet to use and practice the function.

If you using Microsoft 365 but could not find the function, it could be that the Excel version is not from Current Channel.

Excel Filter Function Alternative For Excel 2013, 2016, 2019

FILTER() function is part of a dynamic array formula family. It spills out a range instead of a single cell. This means it can return multiple values without needing you to drag the formula. The alternative could be to use the traditional way in Excel to fill out a range with Index() formulas or the likes.

Pivot Table As an Alternative To Return Multiple Results

Suppose we want to list all records in Full Name, State and Phone columns for a person.

filtering pivot table field

Select the data range. Go to Insert tab, click on “Pivot Table” in Tables group.

Find an appropriate worksheet location to insert the pivot table.

Drag the three fields into Rows field.

row fields arrangement in pivot table

Go to Design tab, for Report Layout, pick “Show in Tabular Form”.

For Subtotals, select “Do not show subtotals”.

You can then click on the filter in Full Name field of the pivot table. Select “Label filters” > Equals, then type the name you want to filter for.

The upside of using a pivot table is that when you put in label filter, you can use wildcard for fuzzy search.

INDEX SMALL IF To Return Multiple Results

You can use the class INDEX SMALL IF array formula to return multiple results. This is perhaps the best alternative to Excel FILTER() function. You can also use it with multiple criteria.

When you use the function, apply Ctrl+Shift+Enter instead of Enter to make it an array formula. Then drag the formula to other ranges.

Suppose we want to see all records from Subscription Date and Full Name columns, where State is in “PA”.

Excel Filter Function Index small if alternative

The formula in cell F8 is

=IFERROR(INDEX($A$2:$D$24,

SMALL(IF($C$2:$C$24=$H$3,

ROW($B$2:$B$24)-MIN(ROW($B$2:$B$24))+1),

ROWS($8:8)),1),

“”)

$A$2:$D$24 is the data range

$C$2:$C$24 is State column, or where criteria is applied.

ROW($B$2:$B$24)-MIN(ROW($B$2:$B$24))+1 this returns the row position of matching records within the data range

ROWS($8:8) lets Excel return 1, 2, 3 and so on, as the formula is dragged down. Combined with SMALL() function, it asks Excel to return the Nth found result.

You will most likely need to adapt the formula to your situation.

Also note that in this example, Full Name column uses 2 as the last argument in Index() function instead of 1. It just tells Excel which column to return. You can change it using Columns() function to be more dynamic, similar to how ROWS() function is used in this case.

DGET As an Alternative To Return One Result

If you are only expecting one result based on your criteria, DGET can be an option.

The upside of DGET compared to pivot table option is that

  • you can change multiple filter criteria easily and
  • when data changes, you can see updated result instantly, as opposed to pressing “Refresh All” button
  • you can use wildcards in DGET

The downside of DGET function is that if it finds multiple matches, it will return an error.

In the screenshot below, the attempt is to find people with the name Garcia where subscription date is after May 31st 2020. Alternatively, if a person is named Martin, and he subscribes after Jan 1st 2023, and he resides in State PA, that would be acceptable as well.

If a #NUM! error is returned, you will need to specify a stricter criteria so that it will only return one result.


RELATED POSTS

Excel Dynamic Search using Filter Function

XLOOKUP Zero to Hero

Pin It on Pinterest

Share This