Skip to content

Formulas to Find Nth Largest Value with Criteria in Excel!

Listen to this article

For instance, you have a list that contains data like names, gender, and scores, If you want to find the highest score based on gender, what would you do? Excel provides a formula to find this without any delay. In this tutorial, we are going to look at how to find the Nth largest value based on certain criteria in Excel. Let’s get into this article!! Get an official version of MS Excel from the following link:

Formula to get Nth largest valuePin
Formula to get Nth largest value

General Formula

Here, we come up with a simple formula to find the Nth largest value from the criteria in the Excel spreadsheet.


Syntax Explanations

  • LARGE – This function returns the Nth largest value from the given range of data. Read more on the LARGE Function.
  • IF – The Excel IF function will help to return one value for a TRUE result, and another for a FALSE result.
  • Range– It represents the input values from the given worksheet.
  • Comma symbol (,) – It is a separator that helps to separate a list of values.
  • Parenthesis () – The main purpose of this symbol is to group the elements.
  • N – It represents the Nth largest value.

How to Apply Formula to Get Nth Largest Value?

Here we will combine multiple functions of Excel such as LARGE and IF function to find the Nth largest value from the range of input values in the spreadsheet. For instance, we will need to get the highest total mark from the students’ list, so that we can find out who gets first rank and that person should get a batch.

The following image will show you a list of students and their corresponding scores in the examination.

Students Total marksPin
Students Total marks

After that, you can use the below-mentioned formula in any cell where you want to get the output result.


Enter the formulaPin
Enter the formula

Once you have entered the formula, click on the Enter button to execute it and get the result.


If the above-given scenario is not enough to understand this formula and want to learn this with a simple example, kindly move to the following section.

Alternate Examples

Here we come up with a simple example to know how to apply this Excel formula in the spreadsheet.

  • In the below example image, you can see the fruit sales details in the range B3:D7. And we want to get the 2nd largest value from the list.
Fruit detailsPin
Fruit details
  • Then, enter the given formula in a cell where you want to get the result.


Type the formulaPin
Type the formula
  • Finally, you will need to click the Enter button to get the result, as shown in the below screenshot.


From this guide, you can easily understand the simple formulas to find the Nth largest values based on certain criteria in Excel. Let me know if you have any doubts regarding this article or any other article on this site. To learn more, check out our website Geek Excel and Excel Formulas.

Video Tutorial

The following video will show you how to use an Excel formula to get the Nth largest value from the given input range.

Also Read:


Leave a Reply

Your email address will not be published. Required fields are marked *

Share to...