Get nth Largest Value with Duplicates

This post will discuss that how to find and get the Nth largest unique value in a range of cells in Excel. Normally, you can use the LARGE function to get the first or second or nth largest value, but it cannot be filtered the duplicated values.

Get nth Largest Value with Duplicates

If you want to get the largest unique value in a data set with duplicates, you can create a new excel array formula based on the MAX function and the IF function.

Assuming you have a data set with duplicates in a range of cells C1:C6, and you want to extract the First and second largest unique values.

Excel 45
Word 34
Access 66
PPT 435
Outlook 443
Excel 443

 

To get the Nth largest values with duplicates, just do the following steps:

#1 you need to get the first largest value using MAX function. Enter the following formula into the formula box in cell E1:

=MAX(C1:C6)

get nth largest value1

#2 you need to write down the below array formula to get the second or nth (n>1) largest unique value:

{=MAX(IF($C$1:$C$6< E1,$C$1:$C$6))}

Enter this formula into the Cell E2, then press Ctrl + Shift + Enter shortcuts to get the second largest value.

get nth largest value2


Related Formulas

  • Get nth Match with One Criteria using INDEX/MATCH
    if you want to find the 2th occurrence of the member “jenny” in the range B2:B10 and extracts its relative bonus value in the range D2:D10, you can used the following array formula:=INDEX(D2:D10, SMALL(IF(B2:B10=”jenny”, ROW(B2:B10)-ROW(INDEX(B2:B10,1,1))+1),2))…
  • Lookup the Value with Multiple Criteria
    If you want to lookup the value with multiple criteria in a range, you can use a combination with the INDEX function and MATCH function to create an array formula.…
  • Lookup the Next Largest Value
    If you want to get the next largest value in another column, you can use a combination of the INDEX function and the MATCH function to create an excel formula..
  • Find the nth Smallest Value
    You can use the SMALL function to get the 1st, 2nd, 3rd, or nth smallest value in an array or range. Also you can use the SMALL function within the INDEX function to extract the relative value of the same row…

Related Functions

  • Excel LARGE function
    The Excel LARGE function returns the largest numeric value from the numbers that you provided. Or returns the largest value in the array. The syntax of the LARGE function is as below:= LARGE (array,nth)…
  • Excel MAX function
    The Excel MAX function returns the largest numeric value from the numbers that you provided. Or returns the largest value in the array.= MAX(num1,[num2,…numn])…
  • Excel IF function
    The Excel IF function perform a logical test to return one value if the condition is TRUE and return another value if the condition is FALSE. The IF function is a build-in function in Microsoft Excel and it is categorized as a Logical Function.The syntax of the IF function is as below:= IF (condition, [true_value], [false_value])….
Related Posts
How to Find the Earliest and Latest Date in Excel
Find the Earliest and Latest Date 10

We have a range of dates and we want to look up the earliest and the latest date based on certain criteria like the earliest date for a showing movie, we can use MIN and MAX functions with IF function ...

How to Create Dynamic Drop Down List without Blank in Excel
create dynamic drop down list with blank5

This post will guide you how to create dynamic drop down list without blank cells in Microsoft Excel. In Excel, and you can use Data Validation feature to improve the efficiency of data entry in excel, and it also be ...

How to Calculate the Average Excluding the Smallest & Highest Numbers in Excel
Calculate the Average Excluding the Smallest & Highest 10

Calculating the average for a batch of data is frequently used in our daily life. But for some cases like statistic the average score in a competition, or price analysis, we often calculate the average excluding the smallest and highest ...

How to Get the Maximum or Minimum Absolute Value in Excel
Get the Maximum or Minimum Absolute Value 10

It is easy to find the maximum or minimum value in a batch of data in excel, but if this batch of numbers contains both positive and negative numbers, the maximum or minimum absolute value cannot be found out by ...

How to Find the Largest Value in a Column and Return the Adjacent Cell Value in Excel
find largest value return adjacent cell value2

This post will guide you how to find the largest value in a column and then return the adjacent cell value with a formula in Excel. How do I find the max value and return the cell value from the ...

How to Extract the Column Header of the Largest Value in a Row in Excel
extract column header of the largest value in row1

This post will guide you how to find the largest value in a row and return column header in Excel. How to retrieve the column header of the largest value in a row with a formula in Excel. How to ...

How to Find Max And Min Value with Single or Multiple Criteria in Excel
find max value based single criteria5

This post will guide you how to find the maximum value in a range of cells based on single or multiple criteria in Excel. How do I calculate the Minimum value based on criteria with MIN Function in Excel. Find ...

How to Check If value Is between Two Numbers in Excel
check value is between two numbers2

This post will guide you how to check if a cell value is between two numbers in Excel. How do I build an IF statement that test if a cell value is between two given values in formula in Excel. ...

How to Get Cell Address of Max or Min Value in a Range in Excel
get cell address of max value2

This post will guide you how to return the cell address of a maximum cell value from a given range of cells with a formula in Excel. How do I Find the minimum value from a given range and return ...

How to Highlight The Highest and Lowest Value in Each Row or Column in Excel
highlight highest value in row11

This post will guide you how to highlight the highest value in each row or column in Excel. How do I highlight the lowest value in each row and column with conditional formatting function. Highlight Highest Value in Each Row ...

Sidebar