LARGE IF formula in Excel: find highest values with criteria

Basic LARGE IF formula in Excel

To get the n-th largest value in a dataset with condition, you can use the LARGE and IF functions together:

Where n is the 1^st, 2^nd, 3^rd, etc. highest value to return.

Please note that it's an array formula that must be entered by pressing the Ctrl + Shift + Enter keys simultaneously. In Excel 365, where dynamic arrays are native, it can be entered normally by pressing the Enter key.

To see the formula in action, please consider the following example.

In the table below, supposing you want to find the biggest 3 scores in different subjects. To have it done, we input the subject names in separate cells (F2, G2 and H2), and then use this formula:

=LARGE(IF($B$2:$B$15=F$2, $C$2:$C$15), $E3)

Where B2:B15 is a list of subjects (criteria_range), C2:C15 are the scores (values), and E3 is n.

Of course, you can hardcode the subject name in the formula (e.g. $B$2:$B$15="Science"), but using a cell reference makes the solution more flexible.

The above formula goes to F3, and then you drag it 2 rows down and 2 columns to the right. Thanks to the skillful use of absolute and mixed cell references, the formula adjusts automatically when copied to other cells, letting you get all the results in one go!

If needed, you can even do without typing n numbers on the sheet. Instead, generate them directly in the formula by embedding the ROWS function with an expanding range reference like this:

=LARGE(IF($B$2:$B$15=E$2,$C$2:$C$15), ROWS(A$2:A2))

The expanding range mechanics is explained in detail in this example, and here I will just show the result:

In case a specific largest value with a given condition is not found, the formula would return a #NUM! error. To prevent this from happening, wrap the LARGE IF formula inside the IFERROR function and replace the error with any value that you see fit, e.g. with a "-" or "Not found":

=IFERROR(LARGE(IF($B$2:$B$15=$F$2, $C$2:$C$15), $E3), "Not found")

How this formula works:

As you may know, the Excel LARGE function returns the n-th highest value specified in the 2^nd argument from an array defined in the 1^st argument. In our case, the array needs to be limited to only the scores in a given subject. To have it done, we embed an IF statement into the array argument, which compares each subject in B2:B15 with the target one in F2 (History):

IF($B$2:$B$15=$F$2, $C$2:$C$15)

Because IF performs the logical test on an array of numbers, the output is also an array, where the History scores are represented by numbers, and all other scores by FALSE values:

{FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;247;FALSE;FALSE;235;FALSE;210;FALSE;FALSE}

The LARGE function ignores errors in the array and finds a specific n-th largest value among the History scores.

Excel LARGE IF with multiple criteria

To check several conditions in one formula, use one of the following methods:

Nest multiple IF statements one into another:

Multiply several range=criteria expressions:

In pre-dynamic Excel versions, both should be entered as array formulas by pressing Ctrl + Shift + Enter. In Excel 365, they can work as regular formulas as well.

And now, let's try these techniques on our sample table. For this, we will add a new column, named Group, and use one of the below formulas to return top 3 scores in a given subject within a certain group:

=LARGE(IF($B$2:$B$15=$G$1, IF($C$2:$C$15=$G$2, $D$2:$D$15)), F6)

=LARGE(IF(($B$2:$B$15=$G$1) * ($C$2:$C$15=$G$2), $D$2:$D$15), F6)

Where:

• B2:B15 are the groups (criteria_range1)
• G1 is the target group (criteria1)
• C2:C15 is the list of subjects (criteria_range2)
• G1 is the target subject (criteria1)
• D2:D15 are the scores (values)
• F6 is n

Enter the formula in either one in G6 and copy it through G8. As the result, you will get the biggest 3 Science scores in group A.

When appropriate, you can put the criteria directly in the formula like this:

=LARGE(IF(($B$2:$B$15="A") * ($C$2:$C$15="Science"), $D$2:$D$15), F6)

But please remember this will require updating the formula each time the criteria change.

How these formulas work:

In the above formulas, we use the IF statements to test two different criteria, so only the values for which both conditions are TRUE make it into the LARGE function's array.

Formula 1 (nested IFs):

The logical test of the first IF statement compares the list of groups against the target one: $B$2:$B$15=$G$1. The result of the test is an array of TRUE and FALSE values, where TRUE's represent group A and FALSE any other group:

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

In a similar manner, the second IF compares the list of subjects with the target one (Science) and returns another TRUE and FALSE array:

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

For the items that have TRUE in both arrays, the nested IF formula returns the scores (value_if_true). The items that do not satisfy the criteria are represented by the FALSE values:

{287;FALSE;275;FALSE;FALSE;FALSE;FALSE;FALSE;237;FALSE;FALSE;FALSE;190;FALSE}

This final array is handed over to the LARGE function, from which it picks the n-th largest value.

Formula 2 (multiplying criteria):

In this formula, we evaluate two different criteria within a single logical test. The multiplication operation converts the logical values to 1's (TRUE) and 0's (FALSE). And because multiplying by 0 always gives zero, the resulting array has 1 for the items that meet both criteria:

{1;0;1;0;0;0;0;0;1;0;0;0;1;0}

The IF function evaluates this array 1's and 0's and passes the scores corresponding to 1's to LARGE.

LARGE IF formula with multiple criteria (OR logic)

To get the LARGE IF formula to work with the OR logic, i.e. when this or that condition is TRUE, sum the range=criteria expressions instead of multiplying them:

Supposing you want to find the largest 3 scores in two different subjects, say History and Literature. For Excel, you formulate the task differently: return the n-th largest score if Subject is either History OR Literature.

With criteria 1 (Literature) in F1 and criteria 2 (History) in F2, the formula goes as follows:

=LARGE(IF(($B$2:$B$15=$F$1) + ($B$2:$B$15=$F$2), $C$2:$C$15), E6)

Where B2:B15 are the subjects, C2:C15 are the scores, and E6 is the n-th largest value to return.

Complete the formula by pressing the Ctrl + Shift + Enter keys together, and you will get this result:

How this formula work:

In array formulas, the addition operation works like the OR operator:

The range=criteria expressions return two arrays of TRUE and FALSE values like those discussed in the previous example. After adding the elements of these arrays, we get an array of 1's and 0's, where 1's correspond to the items that meets either condition and 0's correspond to the items that do not meet any condition:

{0;1;0;1;0;0;1;1;0;1;0;1;0;1}

The IF function checks all the elements of the above array and hands off the scores corresponding to 1's to the LARGE function:

{FALSE;280;FALSE;270;FALSE;FALSE;247;240;FALSE;235;FALSE;210;FALSE;125}

FILTER top n values based on criteria

These solutions use a couple of dynamic array functions which are only available in Excel 356. In Excel 2019, Excel 2016 and earlier versions, these formulas won't work.

In Excel 365, there is one more way to get top N values with conditions. The beauty of this approach is that unlike traditional CSE array formulas the dynamic ones are completed in the usual way by pressing the Enter key. Moreover, the formula needs to be entered just in one cell and fills automatically across the entire spill range.

FILTER n-th largest value with conditions

This solution only works in Excel 365. In other versions, the FILTER function is not available.

The previous examples show how to filter top N values with one or more conditions. If you need to find a specific value, say 2^nd or 3^rd highest number in a dataset, then you can use a much simpler LARGE FILTER formula.

The logic is basically the same as in LARGE IF formulas discussed in the first part of this tutorial. The difference is that you use the FILTER function instead of IF to apply the criteria.

Practice workbook for download

LARGE IF formula examples (.xlsx file)