- Gašper Kamenšek
- September 10, 2019
- 15 Comments

Last year, Microsoft announced the introduction of a new group of functions in Excel, known as dynamic array functions. One of these – the FILTER function – is possibly the best of the lot.

The FILTER function will filter a list and return the results that meet the criteria that you specify. This criteria can include multiple conditions and also AND/OR criteria.

**Note:** The dynamic array formulas are coming soon to Excel 365 subscribers only. Perpetual Excel 2019 license will not have dynamic arrays included.

The filter tool is one of the most useful features of Excel.

Sure, it may not be as flashy as Power Query, PivotTables and advanced formulas. But you watch Excel users at work, and everybody filters lists.

Well, we will now have filtering in the shape of a formula. Meaning it is automated, can return results to wherever you want them, and can be used in other formulas and Excel features.

Yes, you heard me right. The FILTER function could be used in a Data Validation rule or within a SUM function for a new and improved SUMIFS. Opportunities are boundless.

It can also be used as a supercharged lookup formula. Most Excel users are very familiar with VLOOKUP. It’s awesome!

However, it has limitations. Always looks in the first column, can only return one result, only tests the one criteria (without outside help).

The FILTER function (as you will see) can test multiple criteria, and from any column. It can return one (if looking for a unique value), but also multiple results.

Back in March 2018, Gašper wrote a brilliant two part article on Merge Queries being an alternative to VLOOKUP. Well, there is now a powerful formula alternative.

The FILTER function needs the following information to work.

**Array** – This is the range or array of values that you want filtered, and returned by the FILTER function.

**Include** – The criteria that you want to filter the range or array by. This can be any expression that results in TRUE/FALSE. The height of the range tested must be equal to the one provided in the array argument.

**If_empty** – An optional argument. This is the value to return if no records meet the given criteria.

Let’s look at some examples of the FILTER function in action.

We will start with a simple text filter and filter the data below to only see the employees from Washington.

The following formula returns all columns of data if “Washington” is found in column C. Notice the height of the *Array* and *Include* range of equal.

=FILTER(A2:F17,C2:C17=”Washington”,”No results”)

The results are positioned under the data set here for better visuals for the article. Filter function can return the results to a different sheet or workbook, no problem.

Formula was entered into cell A20 but spilled into the range A20:F23.

The spill range is identified by a blue border.

This spill effect is a unique feature of the new dynamic array formulas. The formula only resides (and be edited) in cell A20, but its results spill to the array of cells outside.

If there was content in the area it needed to use, a SPILL error would be shown.

This is caused below by the 0 in cell E22.

However it is easily fixed by simply deleting or moving the naughty content.

If you make a mistake in the formula criteria such as a mistype on a city name. You will receive the CALC! error

Advanced Excel users will have probably worked with array formulas before. They can be very useful, but can also be awkward to create, especially for the inexperienced.

One of the aspects of them that can make them awkward, is the need to press Ctrl + Shift + Enter to execute them.

Good news! The FILTER function can work with and produce a range of results without the need for Ctrl + Shift + Enter.

I have mentioned how FILTER can be an alternative to the many uses of the ever popular VLOOKUP function.

Now this article would be mega if we went through all the possible cases, so let’s just see it performing a classic VLOOKUP scenario.

We would like to return the information for employee B140.

The following FILTER function is entered in cell B20.

=FILTER(B2:F17,A2:A17=A20,”No match”)

There are lots of take-aways from this example.

We can see the FILTER function returning information from columns B:F, but testing column A. So the criteria range does not need to be included in the returned range.

We are also testing a value from a cell (A20).

And final we have a built-in error response. If no value is found then the text “No match” is returned.

No IFNA or IFERROR functions needed.

How about combining the FILTER function with an aggregation function such as SUM, COUNT or AVERAGE. That would be cool.

In this example, we will return the average salary for employees at the London office.

The following formula is entered in cell I3.

=AVERAGE(FILTER(F2:F17,C2:C17=H3,0))

The FILTER function returned the array of values from column F that met the criteria, and the AVERAGE function performed its task on them.

If no results are returned, the value of 0 is shown.

We rely so much on functions such as SUMIFS, COUNTIFS, SUMPRODUCT, AGGREGATE, VLOOKUP and the INDEX & MATCH combo.

And here we are seeing a new function with the ability to take on the tasks we have relied on these different functions for.

So far, we have only performed one logical test in the FILTER criteria. So, lets round this article off by exploring multiple conditional filters.

Let’s begin by using AND logic for two columns of data. We want to return the employees from the London office, who have joined since the 1^{st} January 2017.

The following formula is entered into cell A20 to return 3 employees details.

=FILTER(A2:F17,(C2:C17=”London”)*(D2:D17>=DATE(2017,1,1)),”No results”)

The multiply operator (*) is used to perform AND logic tests, and each Boolean expression is enclosed in its own set of brackets.

If you have used array formulas or functions like SUMPRODUCT before, this approach will not be new. But here is a breakdown if your interested in how it works.

The two conditions are both evaluated resulting in True, and false for each employee.

=FILTER(A2:F17,

{FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;TRUE;FALSE}*

{TRUE;FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE}

,”No results”)

Two arrays containing the results of the conditions are then multiplied producing a 1 only where there is a True for both arrays.

=FILTER(A2:F17,{0;0;0;0;1;1;0;0;0;0;0;0;0;1;0;0},”No results”)

The 1’s represent the 3 employees returned by the FILTER function.

This is how it looks if we break it down into three separate calculations next to the data set.

You can include more than two conditions by continuing the same logic of surrounding each expression in its own brackets and using the multiply operator.

OR logic can be used in the FILTER function in the same manner but using the plus operator (+) instead.

Let’s return the employees from both the Sydney and the Toronto offices.

=FILTER(A2:F17,(C2:C17=”Sydney”)+(C2:C17=”Toronto”),”No results”)

The breakdown from this is the same as before, however the two arrays are added to each other.

And this is how it works. But really just remember that you multiply for AND logic and add for Or logic.

You can also see in this example that the last few results do not have the correct formatting for the date and salary values. Although the dynamic formulas automatically spill into ranges, you will still need to format the cells just like with regular formulas.

Ok, let’s finish off by combining the two logics into one FILTER function.

We will combine the two examples we have done so far and return all employees from both the Sydney and Toronto offices whom started since the 1^{st} January 2017.

=FILTER(A2:F17,((C2:C17=”Sydney”)+(C2:C17=”Toronto”))*(D2:D17>=DATE(2017,1,1)),”No results”)

Notice the OR logical test has been enclosed in another set of brackets here. This is important.

Remember the mathematical rules that multiplication takes priority over addition. Without the brackets around the OR logic, the formula would return the employees from Toronto who started since the 1^{st} January 2017, and then all the Sydney employees.

=FILTER(A2:F17,(C2:C17=”Sydney”)+(C2:C17=”Toronto”)*(D2:D17>=DATE(2017,1,1)),”No results”)

It would fail to test the Sydney employees against the start date.

We would need to specify the OR logic first, then only the once since that start date.

=FILTER(A2:F17,((C2:C17=”Sydney”)+(C2:C17=”Toronto”))*(D2:D17>=DATE(2017,1,1)),”No results”)

The FILTER function is worth exploring further because it has huge potential to assist us in our Excel endeavours.

Especially when it is combined with the other dynamic array functions such as SORT and UNIQUE, and also existing functions as shown in this article such as AVERAGE.

- September 14, 2021

The Excel SEQUENCE function In this article, we’ll ...

This site uses Akismet to reduce spam. Learn how your comment data is processed.

What if I have a selection criteria, like a city, and then a type of retailer as say two options, so I can select in my array the city and type of retail shop to return all that match. But what if I have a drop-down and I only want to select the retailer occasionally, what do I put in for the second part of the equation (ie the in the second bracket with “*”) to select all retailers. Ty, Will.

You could handle that with an IF statement, checking if the retailer is selected and then returning two different Filter functions.

Hello,

Is there a way to do a top 10 filter list. Say top 10 salaries? Or Filter function wouldn’t work here?

Thanks

Hello,

I’m currently using Excel 2016 and the formula isn’t avail. Pls guide.

The Filter function is only available in Office 365.

My Excel 2019 doesn’t have Filter function. It only has FILTERXML.

What can I do?

The Filter function is only available in Office 365.

Nice post, thanks! One question: is there any way to limit the number of rows you receive from spill? E.g. the FILTER gives you 10 rows, is there any way to limit that set to 5 rows?

You can add an Index and filter by that one using the Large function to get the index of the fifth filtered value and then limit the index with another filter.

Hi Gasper,

The Filter Function whilst a great function to have, I have found an issue that I cannot seem to over come.

I have a date column in “N” and a Status Change in “O”. When the cells in “O” change, cells in “N” will automatically add todays date.

There are two reports

Single Date range

=FILTER(tblAccessCards, tblAccessCards[Status Change Date]=$E$8,”No Result”)

From / To Date range

=FILTER(Card_Data!A5:O2500,(Card_Data!O5:O2500>= $C$8)*(Card_Data!O5:O2500<=$D$8), "No Result")

The VBA Code is as follows:

Private Sub Worksheet_Change(ByVal Target As Range)

'Updated by Extendoffice 20180830

Dim xCellColumn As Integer

Dim xTimeColumn As Integer

Dim xRow, xCol As Integer

Dim xDPRg, xRg As Range

xCellColumn = 14

xTimeColumn = 15

xRow = Target.Row

xCol = Target.Column

If Target.Text “” Then

If xCol = xCellColumn Then

Hopefully others might find this helful.

It is driving me insane.

Keep up the great work.

Cells(xRow, xTimeColumn) = Now()

Else

On Error Resume Next

Set xDPRg = Target.Dependents

For Each xRg In xDPRg

If xRg.Column = xCellColumn Then

Cells(xRg.Row, xTimeColumn) = Now()

End If

Next

End If

End If

End Sub

The VBA Code without the interuption

The VBA Code is as follows:

Private Sub Worksheet_Change(ByVal Target As Range)

‘Updated by Extendoffice 20180830

Dim xCellColumn As Integer

Dim xTimeColumn As Integer

Dim xRow, xCol As Integer

Dim xDPRg, xRg As Range

xCellColumn = 14

xTimeColumn = 15

xRow = Target.Row

xCol = Target.Column

If Target.Text “” Then

If xCol = xCellColumn Then

Cells(xRow, xTimeColumn) = Now()

Else

On Error Resume Next

Set xDPRg = Target.Dependents

For Each xRg In xDPRg

If xRg.Column = xCellColumn Then

Cells(xRg.Row, xTimeColumn) = Now()

End If

Next

End If

End If

End Sub

Nice article. I was wondering which one is faster or consume less memory if you work with big data, using the OR() functions as described in the last part, or using a combination of ISNUMBER() and MATCH() to get the same result?

That is a great question, Andre. As these Array Formulas tend to be optimized I would instinctively say that they must be faster. That is not tested although. I Will check if someone has the answer.

Is there a way to limit the columns returned by the array? (e.g. You wanted to return the employee name and age, but not date joined, city, or salary)

Sure there is Brandon and funny enough I will be publishing a video on my Excel Olympics YouTube channel explaining that on Tuesday, 9 AM CET. I will drop a link here once it’s live…