# LARGE IF formula in Excel: get n-th highest value with criteria (2023)

The tutorial shows how to use the Excel LARGE IF formula with one or several criteria.

While working with numeric data in Excel, you may often want to find largest numbers. In a big dataset, there may be a need to narrow down the results by using one or more criteria. The bad news is that the LARGEIF function does not exist in Excel. The good news is that you can easily construct your own LARGE IF formula :)

• LARGE IF formula in Excel
• LARGE IF with multiple criteria (AND logic)
• LARGE IF with multiple criteria (OR logic)
• Filter top n values based on criteria
• FILTER n-th largest value with conditions

## 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:

{=LARGE(IF(criteria_range=criteria, values), n)}

Where n is the 1st, 2nd, 3rd, 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 2nd argument from an array defined in the 1st 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:

(Video) Excel MAX or MIN with CONDITIONS (MAXIFS & AGGREGATE Method)

Nest multiple IF statements one into another:

{=LARGE(IF(criteria_range1=criteria1, IF(criteria_range2=criteria2, values)), n)}

Multiply several range=criteria expressions:

{=LARGE(IF((criteria_range1=criteria1) * (criteria_range2=criteria2), values), n)}

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:

(Video) Excel formula to find the nth Largest value

`{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:

{=LARGE(IF((criteria_range1=criteria1) + (criteria_range2=criteria2), values), n)}

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.

### Formula 1. Filter top n values with one condition

To find the largest values based on condition, use this generic formula:

SORT(FILTER(values, (values >=LARGE(IF(criteria_range=criteria, values), n)) * (criteria_range=criteria)), 1, -1)

Where n is the number of top entries to extract.

For example, to get top 3 scores in a given subject, the formula in E4 is:

`=SORT(FILTER(C2:C15, (C2:C15>=LARGE(IF(B2:B15=F1, C2:C15), 3)) * (B2:B15=F1)), 1, -1)`

Where C2:C15 are the scores, B2:B15 are the subjects, and F1 is the subject of interest. Because the formula is entered just in one cell, you needn't bother about locking the ranges and cells with absolute references. If you'd like to retrieve not only the scores but the related data as well, make the following adjustments to the formula:

• For the array argument of FILTER, supply the entire table (A2:C15).
• In the sort_index argument of SORT, indicate the number of the column to sort by (3rd column in our case).

`=SORT(FILTER(A2:C15, (C2:C15>=LARGE(IF(B2:B15=F1, C2:C15), 3)) * (B2:B15=F1)), 3, -1)` (Video) Excel Magic Trick # 162: LARGE IF & INDEX w 4 Criteria

How this formula works:

The Excel FILTER function filters an array of data based on the criteria in the include argument. In our case, the criteria are as follows:

`(C2:C15>=LARGE(IF(B2:B15=F1, C2:C15), 3)) * (B2:B15=F1))`

The left part of the expression selects the scores that are greater than or equal to the 3rd highest score in the target group:

`(C2:C15>=LARGE(IF(B2:B15=F1, C2:C15), 3))`

Here, the IF function checks if the Subject is equal to the target one in F1 (Science) and passes the corresponding scores to LARGE. All other scores are replaced with FALSE:

`{FALSE;190;FALSE;230;FALSE;237;FALSE;FALSE;252;268;FALSE;275;FALSE;287}`

In the above array, the LARGE function finds the 3rd largest score, which is 268.

So, our criteria simplify to:

`(C2:C15>=268) * (B2:B15=F1)`

And the FILTER function resolves to:

`FILTER(C2:C15, {0;0;0;0;0;0;0;0;0;1;0;1;0;1})`

Where 1's correspond to scores greater than or equal to 268 in Science.

Finally, FILTER returns an array of top 3 scores:

`{268;275;287}`

The SORT function with the sort_order argument set to -1 arranges these scores in descending order and spills the results into E4:E6.

### Formula 2. Filter top n values with multiple AND criteria

To filter the highest values with two or more conditions, you need to evaluate several logical expressions instead of one:

SORT(FILTER(values, (values >=LARGE(IF((criteria_range1=criteria1) * (criteria_range2=criteria2), values), n)) * (criteria_range1=criteria1) * (criteria_range2=criteria2)), 1, -1)

For example, to get top 3 scores among students of a particular group (G1) in a specific subject (G2), you could use this formula:

`=SORT(FILTER(D2:D15, (D2:D15>=LARGE(IF((B2:B15=G1)*(C2:C15=G2), D2:D15), 3)) * (B2:B15=G1)*(C2:C15=G2)), 1, -1)`

Where B2:B15 are the groups, C2:C15 are the subjects and D2:D15 are the scores.

To get the scores as well as the associated data, the formula in F5 is:

`=SORT(FILTER(A2:D15, (D2:D15>=LARGE(IF((B2:B15=G1)*(C2:C15=G2), D2:D15), 3)) * (B2:B15=G1)*(C2:C15=G2)), 4, -1)` ### Formula 3. Filter top n values with multiple OR criteria

To evaluate several criteria using OR logic, we added up logical expressions in the LARGE IF formula. This approach works for the FILTER formula too:

SORT(FILTER(values, (values >=LARGE(IF((criteria_range1=criteria1) + (criteria_range2=criteria2), values), n)) * ((criteria_range1=criteria1) + (criteria_range2=criteria2))), 1, -1)

(Video) Excel IF Formula: Simple to Advanced (multiple criteria, nested IF, AND, OR functions)

In our sample table, to retrieve top 3 scores in either Literature (F1) or History (F2), construct the criteria in this way:

`=SORT(FILTER(C2:C15, (C2:C15>=LARGE(IF((B2:B15=F1)+(B2:B15=F2), C2:C15), 3)) * ((B2:B15=F1)+(B2:B15=F2))), 1, -1)`

Where B2:B15 are the subjects and C2:C15 are the scores.

To extract not only the scores but all the related information, the formula in E5 is:

`=SORT(FILTER(A2:C15, (C2:C15>=LARGE(IF((B2:B15=F1)+(B2:B15=F2), C2:C15), 3)) * ((B2:B15=F1)+(B2:B15=F2))), 3, -1)` ## 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 2nd or 3rd 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.

### Formula 1. Find n-th highest value with condition

For one condition, this basic formula will work:

LARGE(FILTER(values, criteria_range=criteria), n)

For example, to get the 3rd highest score in Literature, use one of these formulas:

`=LARGE(FILTER(\$C\$2:\$C\$15, \$B\$2:\$B\$15="Literature"), 3)`

`=LARGE(FILTER(\$C\$2:\$C\$15, \$B\$2:\$B\$15=\$F\$1), \$F\$2)`

Where B2:B15 are the subjects, C2:C15 are the scores, F1 is the subject of interest and F2 is the n-th largest score to return. ### Formula 2. Filter n-th largest value using multiple AND criteria

To evaluate two or more criteria using the AND logic, here's the formula to use:

LARGE(FILTER(values, (criteria_range1=criteria1) * (criteria_range2=criteria2)), n)

Supposing you are looking for the 3rd highest score (G3) in Literature (G2) in group B (G1). Supply the corresponding references to the formula:

`=LARGE(FILTER(\$D\$2:\$D\$15, (\$B\$2:\$B\$15=\$G\$1) * (\$C\$2:\$C\$15=\$G\$2)), \$G\$3)`

And you will get this result: ### Formula 3. Get n-th biggest value using multiple OR criteria

To test multiple criteria with the OR logic, the formula is:

LARGE(FILTER(values, (criteria_range1=criteria1) + (criteria_range2=criteria2)), n)

Let's say you wish to find the 5th highest score (F3) in humanitarian subjects - Literature (F1) or History (F2). The formula takes this shape:

`=LARGE(FILTER(\$C\$2:\$C\$15, (\$B\$2:\$B\$15=\$F\$1)+(\$B\$2:\$B\$15=\$F\$2)), \$F\$3)`

It will check if condition 1 or condition 2 is TRUE and output the result: That's how to use Excel LARGE function with criteria. I thank you for reading and hope to see you on our blog next week!

(Video) Mr Excel & excelisfun Trick 133: Add Nth Largest Values With Three Criteria Dynamic Formulas

LARGE IF formula examples (.xlsx file)

## You may also be interested in:

• SMALL IF formula to get smallest values with criteria
• How to find top and bottom values in Excel
• How to highlight top values in Excel
• How to sum largest N numbers in Excel

## FAQs

### How do you find the highest value in Excel with condition? ›

The MAXIFS function in Excel can get the highest value based on one or multiple criteria. By default, Excel MAXIFS works with the AND logic, i.e. returns the maximum number that meets all of the specified conditions. For the function to work, the max range and criteria ranges must have the same size and shape.

How do I use large functions in Excel with criteria? ›

The formula would have the syntax {=LARGE(IF((criteria 1)*(criteria 2),value),n)} . This will find the nth largest value based on multiple criteria. Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function.

Which function can be used to get and highest value? ›

The MAX function syntax has the following arguments: Number1, number2, ... Number1 is required, subsequent numbers are optional. 1 to 255 numbers for which you want to find the maximum value.

What is Dmax in Excel? ›

Description. Returns the largest number in a field (column) of records in a list or database that matches conditions you that specify.

How to return the largest number in a range based on your supplied criteria? ›

MAX will return the largest value in a given list of arguments. From a given set of numeric values, it will return the highest value. Unlike MAXA function, the MAX function will count numbers but ignore empty cells, text, the logical values TRUE and FALSE, and text values.

How does Xlookup work in Excel? ›

The XLOOKUP function searches a range or an array, and then returns the item corresponding to the first match it finds. If no match exists, then XLOOKUP can return the closest (approximate) match. *If omitted, XLOOKUP returns blank cells it finds in lookup_array.

Which function is used to find the largest value in the given range *? ›

The MAX function returns the largest numeric value in the data provided. The MAX function can be used to return the largest value from any type of numeric data.

How does an H lookup work? ›

A logical value that specifies whether you want HLOOKUP to find an exact match or an approximate match. If TRUE or omitted, an approximate match is returned. In other words, if an exact match is not found, the next largest value that is less than lookup_value is returned. If FALSE, HLOOKUP will find an exact match.

What is the formula for maximum value? ›

Determining the Maximum Value of the Quadratic Equation

For instance, it can be stated that if the equation has been represented in the form of ax2 +bx+c, the formula for finding the maximum value will be max= c- (b2/4a).

How do you use the max function? ›

The Excel MAX Formula is used to find out the maximum value from a given set of data/ array. MAX function in Excel returns the highest value from a given set of numeric values. Excel MAX formula will count numbers but ignore empty cells, text, the logical values TRUE and FALSE, and text values.

### How do you find the maximum value range? ›

The largest value in a data set is often called the maximum (or max for short), and the smallest value is called the minimum (or min). The difference between the maximum and minimum value is sometimes called the range and is calculated by subtracting the smallest value from the largest value.

Which function returns the largest value of the column? ›

The MAX() function returns the largest value of the selected column.

How do I do an Ifsum in Excel? ›

If you want, you can apply the criteria to one range and sum the corresponding values in a different range. For example, the formula =SUMIF(B2:B5, "John", C2:C5) sums only the values in the range C2:C5, where the corresponding cells in the range B2:B5 equal "John."

How does Daverage work in Excel? ›

The Microsoft Excel DAVERAGE function averages all numbers in a column in a list or database, based on a given criteria. The DAVERAGE function is a built-in function in Excel that is categorized as a Database Function. It can be used as a worksheet function (WS) in Excel.

What does DDD mean in Excel formula? ›

yyyy: Year with four digits. d: Day. dd: Day with a leading 0. ddd: Day of the week (e.g., Mon) dddd: Day of the week written out (e.g., Monday)

How do you find the range of a large data set? ›

The range is calculated by subtracting the lowest value from the highest value.

Can I use IFS formula in conditional formatting? ›

One of the methods to achieve Conditional formatting based on a custom formula is using the IF statement. The IF function works on the IF/THEN/ELSE condition syntax.

Can we enter multiple if conditions in an IF formula? ›

Remarks. While Excel will allow you to nest up to 64 different IF functions, it's not at all advisable to do so.

Can you put two conditions in an if statement? ›

The multiple IF conditions in Excel are IF statements contained within another IF statement. They are used to test multiple conditions simultaneously and return distinct values. The additional IF statements can be included in the “value if true” and “value if false” arguments of a standard IF formula.

Can you use Xlookup for multiple values? ›

One more amazing feature of XLOOKUP is its ability to return more than one value relating to the same match.

### Can you combine VLOOKUP and IFS? ›

Did you know that you can use Excel IF statements along with VLOOKUPs? For example, if you wanted to enter a value from a table into a cell, based on what was in another cell, you could start with an IF statement and then enter the VLOOKUP in the “value if true” part of the IF statement.

Which is faster VLOOKUP or Xlookup? ›

XLOOKUP has many amazing capabilities but is slower than VLOOKUP and INDEX MATCH on huge data sets. Hopefully, Microsoft will improve its performance in the future versions. OFFSET MATCH is the fastest in Excel 32-bit. But because of its complex syntax, there a big chance to make a mistake.

Which is better VLOOKUP or Xlookup? ›

The XLOOKUP defaults to an exact match where the VLOOKUP defaults to an approximate match. As the exact match is used most often, this setting would make the XLOOKUP more effective. On top of this, the XLOOKUP offers an additional option of an approximate match returning the next larger value.

What is difference between VLOOKUP and Xlookup? ›

XLOOKUP vs VLOOKUP – Key Differences

XLOOKUP searches for data both horizontally and vertically. VLOOKUP searches only vertically. XLOOKUP can refer to the left of the lookup_value. It always looks up the values towards the right of the lookup_value.

What's the difference between H and VLOOKUP? ›

The most commonly used LOOKUP functions in Excel are VLOOKUP and HLOOKUP. VLOOKUP allows you to search a data range that is set up vertically. HLOOKUP is the exact same function, but looks up data that has been formatted by rows instead of columns.

Is Hlookup and Xlookup the same? ›

XLOOKUP was released by Microsoft in 2019 and is meant as the replacement for VLOOKUP, HLOOKUP, INDEX/MATCH functions.

Can you do a VLOOKUP in an Hlookup? ›

If the data table has row headers and you need to search horizontally across the rows for a “match,” then use HLOOKUP. The main limitation is that the value looked with VLOOKUP needs to be in the left-most table column and in HLOOKUP it has to be in the top row of the data set.

Is Max () a method or function? ›

max() method is the inbuilt method of the Math library. The Math. max() method takes two parameters as an argument and returns the maximum of two.

Can we use MAX function in where clause? ›

The MAX() function is used with the WHERE clause to gain further insights from our data. In SQL, the MAX() function computes the highest or maximum value of numeric values in a column.

How do you find the maximum value of a condition? ›

The IF function is a conditional function that displays results based on certain criteria. The MAX IF function identifies the maximum value from all the array values that match the logical test. The formula of Excel MAX If function is “=MAX(IF(logical test,value_ if _true,value_if_ false)).”

### How do you determine the highest cell value included in the argument? ›

MAX: This function determines the highest cell value included in the argument. MIN: This function determines the lowest cell value included in the argument.

How do you do a Vlookup max value? ›

Vlookup to Return Max

This Vlookup function is exactly the same as the regular one except that the MAX() function is used for the lookup value argument. The MAX() function returns the highest value from the list of numbers and then that value is used to perform the lookup.

Is there a maximum value theorem? ›

The Extreme Value Theorem guarantees both a maximum and minimum value for a function under certain conditions. It states the following: If a function f(x) is continuous on a closed interval [ a, b], then f(x) has both a maximum and minimum value on [ a, b].

## Videos

1. Excel - Large Function with Criteria - array function by Chris Menard
(Chris Menard)
2. Find 1st, 2nd, Nth Highest Matching Value in Excel & Google Sheets and Return Another Cell
3. Find the Nth. Highest Value in a List dynamically in Excel
(Officeinstructor)
4. Excel Lookup and Find the 2nd, 3rd, 4th or Nth Occurrence / Match (Without Using an Array Formula)
(Chester Tugwell)
5. How to find the Top 5 values in Excel with one formula (LARGE formula)
6. Excel LARGE function | Find the second, third largest value | Excel One Minute Quick Reference
(Chris Menard)
Top Articles
Latest Posts
Article information

Last Updated: 12/19/2022

Views: 6317

Rating: 5 / 5 (60 voted)

Author information