Excel 2020: Filter with a Formula
September 16, 2020 - 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].
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
If cell G1 changes from Red to Blue, the results change to show you the blue team records.
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.
To avoid the #CALC! error, add a third argument.
You can specify an array constant for the third argument if you want to fill each column of the answer array.
To filter to records where multiple conditions are met, multiply the conditions together.
Title Photo: Ben Wicks at Unsplash.com
This article is an excerpt from MrExcel 2020 - Seeing Excel Clearly.