LARGE IF & SMALL IF Formulas in Excel & Google Sheets

This tutorial will demonstrate how to calculate “large if” or “small if”, retrieving the nth largest (or smallest) value based­­ on criteria.

 

LARGE & SMALL Functions

The LARGE Function is used to calculate the nth largest value (k) in an array, while the SMALL Function returns the smallest nth value.

=LARGE($D$2:$D$10,1)

To create a “Large If”, we will use the LARGE Function along with the IF Function in an array formula.

LARGE IF

By combining LARGE (or SMALL) and IF in an array formula, we can essentially create a “LARGE IF” function that works similar to how the built-in SUMIF Function works. Let’s walk through an example.

We have a list of grades achieved by students in two different subjects:

Supposed we are asked to find the top three grades achieved for each subject like so:

To accomplish this, we can nest an IF function with the subject as our criteria inside of the LARGE function like so:

=LARGE(IF(<criteria range>=<criteria>, <values range>),<position>)

=LARGE(IF($C$2:$C$10=$F3,$D$2:$D$10),G$2)

When using Excel 2019 and earlier, you must enter the formula by pressing CTRL + SHIFT + ENTER to get the curly brackets around the formula.

How does the formula work?

The formula works by evaluating each cell in our criteria range as TRUE or FALSE.

Finding the top grade value (k=1) in Math:

=LARGE(IF($C$2:$C$10=$F3,$D$2:$D$10),G$2)

=LARGE(IF({TRUE; FALSE;FALSE; TRUE; FALSE; TRUE; FALSE; TRUE; FALSE}, {0.81; 0.8; 0.93; 0.42; 0.87; 0.63; 0.71; 0.58; 0.73}), 1)

Next, the IF Function replaces each value with FALSE if its condition is not met.

=LARGE({0.81;FALSE;FALSE;0.42;FALSE;0.63;FALSE;0.58;FALSE},1)

Now the LARGE Function skips the FALSE values and calculates the largest (k=1) of the remaining values (0.81 is the largest values between 0.42 and 0.81).

SMALL IF

The same technique can also be applied with the SMALL Function instead.

=SMALL(IF($C$2:$C$10=$F3,$D$2:$D$10),G$2)

LARGE IF with multiple criteria

To use LARGE IF with multiple criteria (similar to how the built-in SUMIFS formula works), simply nest more IF functions into the LARGE function like so:

=LARGE(IF(<criteria1 range>=<criteria1>, IF(<criteria2 range>=<criteria2>, <values range>)),<position>)

=LARGE(IF($D$2:$D$18=$H3,IF($B$2:$B$18=$G3,$E$2:$E$18)),I$2)

Another way to include multiple criteria is to multiply the criteria together as shown in this article on calculating the Median If.

Tips and tricks:

• Where possible, always reference the position (k) from a helper cell and lock reference (F4) as this will make auto-filling formulas easier.
• If you are using Excel 2019 or newer, you may enter the formula without CTRL + SHIFT + ENTER.
• To retrieve the names of students that achieved the top marks, combine with this with INDEX / MATCH.