MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Excel 2019: Filter with a Formula

September 11, 2019 - by Bill Jelen

Excel Filter with a Formula. Photo Credit: Ben Wicks at

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.

Title Photo: Ben Wicks at

Bill Jelen is the author / co-author of
MrExcel 2020 - Seeing Excel Clearly

This is a 4th edition of MrExcel LX. Updates for 2020 include: Ask a question about your data, XLOOKUP, Power Query's Data Profiling tools, How Geography Data Types decide which Madison, A SEQUENCE example for descending 52 weeks, Exchange Rates support in Stock Data Types, How to collapse the Search box, How to leave effective feedback for Microsoft, How to post your worksheet to the MrExcel Board using XL2BB.