Large with criteria

In this example, the goal is to display the top 3 values in C5:C16 that match a specific group, entered as a variable in cell F4. If the group is changed, the formulas should calculate new results. For convenience, group (B5:B16) and value (C5:C16) are named ranges. The core of the solution is the LARGE function, which can be used to retrieve the "nth" largest value in a set of data. The challenge is that the LARGE function does not offer any direct way to apply criteria to the values being processed. This means we need to create our own logic to apply conditions in a separate step.

There are two basic ways to approach this problem. In the current version of Excel, you can use the FILTER function to apply conditions to data before it is delivered to LARGE. In older versions of Excel, you can use the IF function in an array formula. Both approaches are explained below.

LARGE function

The LARGE function can be used to return the nth largest value in a set of data. The generic syntax for LARGE looks like this:

=LARGE(range,n)

where n is a number like 1, 2, 3, etc. For example, you can retrieve the first, second, and third largest values like this:

=LARGE(range,1) // first largest
=LARGE(range,2) // second largest
=LARGE(range,3) // third largest

The challenge in this problem is that LARGE has no built-in way to apply criteria. One way to apply criteria is with the FILTER function, as described in the next section below.

LARGE with FILTER

In the current version of Excel, the FILTER function can be used to apply criteria inside of LARGE. This is the approach used in the worksheet shown, where the formula in F7 is:

=LARGE(FILTER(value,group=$F$4),E7)

Working from the inside out, the FILTER function is configured to extract values for the group entered in F4 like this:

=FILTER(value,group=$F$4)

Inside FILTER, array is given as value (C5:C16), and the include argument is provided as an expression that compares each value in group (B5:B16) to the value in F4 ("A"). Note that $F$4 is an absolute reference, because we don't want this reference to change when the formula is copied down column F. Since there are 12 values in B5:B16, the expression returns an array that contains 12 TRUE and FALSE values like this:

{TRUE;FALSE;TRUE;FALSE;TRUE;FALSE;TRUE;FALSE;TRUE;FALSE;TRUE;FALSE}

The TRUE values indicate rows where the group equals "A". This array is used by FILTER to extract matching values. The result from FILTER is an array that contains the 6 values in the data in group "A":

{100;70;50;85;91;96}

These values are provided to the LARGE function as the array argument. The second argument, k, comes from cell E7:

=LARGE({100;70;50;85;91;96},E7)
=LARGE({100;70;50;85;91;96},1)
=100

As the formula is copied down, the value for k changes and LARGE returns the 1st, 2nd, and 3rd largest numbers in group A.

LARGE with IF

In Legacy Excel, the FILTER function does not exist so we do not have a dedicated function to filter values by group. Instead, we can use a traditional array formula based on the IF function like this:

{=LARGE(IF(group=$F$4,value),E7)}

Note: this is an array formula and must be entered with control + shift + enter in Legacy Excel.

In this formula, the IF function provides the same purpose as the FILTER function above: it "filters" the values by group. Since we are running a logical test on a range of cells (B5:B16), we get an array of results. The array returned by IF looks like this:

{100;FALSE;70;FALSE;50;FALSE;85;FALSE;91;FALSE;96;FALSE}

Note that only values in group A make it into the array. Group B values become FALSE since they fail the logical test. This array is returned directly to the LARGE function as the array argument. The value for k comes from cell E7:

=LARGE({100;FALSE;70;FALSE;50;FALSE;85;FALSE;91;FALSE;96;FALSE},E7)
=LARGE({100;FALSE;70;FALSE;50;FALSE;85;FALSE;91;FALSE;96;FALSE},1)
=100

LARGE automatically ignores the FALSE values and returns the largest number in the remaining values, which is 100.

Multiple criteria

To take into account multiple criteria, you can extend the formula with boolean logic. With FILTER, the generic formula looks like this:

=LARGE(FILTER(data,(criteria1)*(criteria2),n)

Where criteria1 and criteria2 represent expressions to test specific conditions. In older versions of Excel, you can use the same approach like this:

=LARGE(IF((criteria1)*(criteria2),values),n)

For more information on using Boolean logic in array formulas, see the video below.