The FILTER function in Excel - KING OF EXCEL

Friday, January 24, 2020

Home Mr King Excel Tips The FILTER function in Excel

The FILTER function in Excel

January 24, 2020 Mr King Excel Tips,

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.

#evba #etipfree #kingexcel
📤You download App EVBA.info installed directly on the latest phone here : https://www.evba.info/p/app-evbainfo-setting-for-your-phone.html?m=1

Crafted with by TemplatesYard