Excel 2024: Filter with a Formula

August 02, 2024 - by Bill Jelen

The FILTER function is new as part of the dynamic arrays feature. There are three arguments: array, include, and an optional [if empty].

The Filter function has three arguments: Array, Include, and an optional [if_empty])

Say you want to be able to enter a team name in G1 and extract all of the records for that team. Use a formula of =FILTER(B3:E9,C3:C9=G1).

With Red entered in G1, you get all of the records for the red team returned to G3:J6.

If cell G1 changes from Red to Blue, the results change to show you the blue team records.

Change G1 from Red to Blue and you get three records returned, representing the rows for the Blue team.

In the above examples, the optional [If Empty] argument is missing. If someone is allowed to enter the wrong team name in G1, then you will get a #CALC! error.

The #CALC! error appears when you type Yellow in G1. Since there are no matching records, the FILTER function is returning an empty array. As of February 2019, empty arrays are not supported in Excel, so you get the #CALC! error. The official definition of #CALC! is "we can't calculate this today, but we might be able to calculate it in the future."

To avoid the #CALC! error, add a third argument.

Use the optional if_empty argument with "None Found" and the #CALC! error changes to "None Found".

You can specify an array constant for the third argument if you want to fill each column of the answer array.

But here is a problem: the FILTER function should have been returning four values. The "None Found" entered in the previous screenshot only fills one column. You can use an array constant of {"No","Team","O",0} to fill all four columns when nothing matches.

To filter to records where multiple conditions are met, multiply the conditions together.

Bonus Tip: Understanding Array Constants

You've just seen a few examples that included an array constant. Here is a simple way to understand them. A comma inside an array constant means to move to the next column. A semi-colon means to move to the next row. How do you remember which is which? The semi-colon on your keyboard is located near the Enter or Return key which also goes to the next row.

On the US keyboard, a semi-colon is near the Enter key. When you see a semi-colon, think "new row". In this screenshot, ={1;2;3} entered in A4 fills A4, A5, A6 with 1, 2, 3. Commas mean new column. A formula of ={1,2,3} entered in A9 fills A9:C9 with 1, 2, 3.

When you see an array constant with a mix of commas and semi-colons, remember that each semi-colon moves to a new row.

Array constants can be a mix of semi-colons and commas. ={1,2,3;4,5,6} entered in G4 will fill G4:I5. You will have 1, 2, 3 in the first row and 4, 5, 6 in the second row.

This article is an excerpt from MrExcel 2024 Igniting Excel

Title photo by Rae Wallis on Unsplash