Using The FILTER Function With One Condition

August 16, 2022 - by Bill Jelen

The new FILTER function accepts an array, keeps the rows you specify, and returns the results to a spill range.

This example uses the following data set,

The data used to illustrate the FILTER function has Names in A, Team in B, and Score in C. Headings are in row 3. Data is in rows 4:16. — Figure 617. Name, team, and score.

The syntax of the FILTER function is FILTER(array, include, [if_empty]).

The syntax for FILTER is Array, Include, and optionally [If Empty].

Figure 618. FILTER

Say that you want to retrieve all rows from the data set where the team is Blue. Type Blue in cell F1. The formula entered in E4 is =FILTER(A4:C16,B4:B16=F1,"None Found").

Notice that you don't have to use the F4 key or dollar signs in the formula. This single formula returns multiple results. There is no need to copy it anywhere, so there is no need for absolute references. Also, you do not have to press Ctrl+Shift+Enter.

Type a team name of Red in cell F1. The FILTER function in E3 is =FILTER(A4:C16,B4:B16=F1,"None Found"). The results show only people on the Red team and spill to E3:G9. — Figure 619. One

If you now type Blue in F1, you get all of the Blue team members.

Change the team in F1 from Red to Blue and the FILTER formula returns the people on the Blue team. Only six rows are returned instead of 7. — Figure 620. Change the value in

The optional third argument of FILTER is illustrated here. [if_empty] specifies the text to return in case there are no results. If you change the value in F1 to Lime, you get the text specified in the formula.

You can specify an array constant as the [if_empty] argument: =FILTER(A4:C16,B4:B16=F1,{"None Found","No Team",0}).

Specify an array constant with three values as the None_Found argument in the FILTER function. =FILTER(A4:C16,B4:B16=F1,{"None Found","No Team",0}). When you choose a team that has no members, all three columns will be populated with default values. — Figure 621. Will the third argument accept an array? Yes.

This article is an excerpt from Power Excel With MrExcel

Title photo by Rae Wallis on Unsplash