Formulas to Find Nth Largest Value with Criteria in Excel!

Ads

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 N^th 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:

https://www.microsoft.com/en-in/microsoft-365/excel

General Formula

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

=LARGE(IF(range=criteria,values),N)

Syntax Explanations

LARGE – This function returns the N^th 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 N^th 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.

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

=LARGE(IF(B3:B10=”Lisa”,C3:C10),1)

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.

Then, enter the given formula in a cell where you want to get the result.

=LARGE(IF(B3:B7=”Kiwi”,D3:C7),2)

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

Wrap-Up

From this guide, you can easily understand the simple formulas to find the N^th 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.