How to Use Excel LARGE Function with Duplicates in Excel

The LARGE function cannot identify the unique nth largest value if duplicates are available in an array. In this article, you will find the nth largest value with duplicates in an array by using Excel LARGE function with duplicates in Excel.

Overview Image


Download Practice Workbook

You may download the following workbook to practice yourself.


3 Ways to Use LARGE Function with Duplicates in Excel

When you do not have unique data, it will not find the unique large value of the array. It shows duplicate values instead of unique ones. So, in this article, we will provide 3 easy methods combining the LARGE function with multiple functions and finding the nth largest value with duplicates.

 


Method 1: Nesting LARGE, COUNTIF, MAX, MIN, INDIRECT, and ROW Functions to Find N-th Largest Value from Data That Contains Duplicates

Nesting the LARGE, COUNTIF, MAX, INDIRECT, MIN, and ROW functions will give the unique Nth large value with duplicates.

Steps:

  • First, we have to write the formula in the H5 cell.

Nesting functions with Excel LARGE function for finding largest value with duplicates

=LARGE(IF(COUNTIF(E$5:E$12,ROW(INDIRECT(MIN(E$5:E$12)&":"&MAX(E$5:E$12)))),ROW(INDIRECT(MIN(E$5:E$12)&":"&MAX(E$5:E$12)))),G5)
  • Then, press ENTER.

Nesting functions with LARGE functions for finding largest value with duplicates

  • Finally, use the AutoFill tool to copy the formula.

Showing Results

Formula Breakdown

  • ROW(INDIRECT(MIN(E$5:E$12)&”:”&MAX(E$5:E$12) gives the row reference of maximum value to the minimum value of the array and the INDIRECT function gives the reference.
  • COUNTIF(E$5:E$12,ROW(INDIRECT(MIN(E$5:E$12)&”:”&MAX(E$5:E$12)))) counts how many numbers are duplicates.
  • IF(COUNTIF(E$5:E$12,ROW(INDIRECT(MIN(E$5:E$12)&”:”&MAX(E$5:E$12)))),ROW(INDIRECT(MIN(E$5:E$12)&”:”&MAX(E$5:E$12)))),G5 the IF function returns the corresponding value highest to lowest without duplicates.
  • =LARGE(IF(COUNTIF(E$5:E$12,ROW(INDIRECT(MIN(E$5:E$12)&”:”&MAX(E$5:E$12)))),ROW(INDIRECT(MIN(E$5:E$12)&”:”&MAX(E$5:E$12)))),G5) gives the large value corresponding to G cell.

Method 2: Merging UNIQUE and LARGE Functions to Find Unique Largest Value

Here in the formula, first, we will figure out the unique values by using the UNIQUE function then we will use the LARGE function to find the unique large function.

Steps:

  • At first, we will type the formula in the H5 cell.
=LARGE(UNIQUE($E$5:$E$12),G5:G8)

Merging UNIQUE function with LARGE function for finding largest values with duplicates

  • After that, press ENTER.
  • And it will give the direct result without using AutoFill.

Showing Results

Formula Breakdown

  • Here, UNIQUE($E$5:$E$12) finds out the unique value from selected E column cells.
  • Then =LARGE(UNIQUE($E$5:$E$12),G5:G8) gives the relevant large function with column G.

Method 3: Nesting IFERROR, FREQUENCY, MATCH, and ROW with LARGE Function with Duplicates to Find Unique Largest Value

Here, we pluck the unique values and then use the LARGE function by nesting the IFERROR, FREQUENCY, MATCH, and ROW functions.

Steps:

  • First, we will use the formula in the H5 cell.
=IFERROR(LARGE(IF(FREQUENCY(IF(E$5:E$12<>"",MATCH(E$5:E$12,E$5:E$12,0)),ROW(E$5:E$12)-ROW(E$5)+1), E$5:E$12), ROW(E1)),"")

Nesting IFERROR, FREQUENCY, MATCH, and ROW with LARGE Function for finding largest value with duplicates

  • Then, we will click on ENTER.

Nesting IFERROR, FREQUENCY, MATCH, and ROW with LARGE Function for finding largest value with duplicates

  • Then, apply the AutoFill tool to get the final result.

Showing results

Formula Breakdown

  • IF(E$5:E$12<>””,MATCH(E$5:E$12,E$5:E$12,0)) in this function the MATCH function finds out the corresponding relative position when they do not find the relative duplicate value. If they find a duplicate, the number indicating the relative position will be identical.
  • FREQUENCY(IF(E$5:E$12<>””,MATCH(E$5:E$12,E$5:E$12,0)),ROW(E$5:E$12)-ROW(E$5)+1), E$5:E$12) here frequency gives the how many times same values are repeated and makes the corresponding position value become zero after first one occurs.
  • IF(FREQUENCY(IF(E$5:E$12<>””,MATCH(E$5:E$12,E$5:E$12,0)),ROW(E$5:E$12)-ROW(E$5)+1), E$5:E$12), ROW(E1)) here we find the sorted value lowest to highest and the position of repeated value becomes false.
  • LARGE(IF(FREQUENCY(IF(E$5:E$12<>””,MATCH(E$5:E$12,E$5:E$12,0)),ROW(E$5:E$12)-ROW(E$5)+1), E$5:E$12), ROW(E1) gives the sorted value of lowest to highest.
  • IFERROR(LARGE(IF(FREQUENCY(IF(E$5:E$12<>””,MATCH(E$5:E$12,E$5:E$12,0)),ROW(E$5:E$12)-ROW(E$5)+1), E$5:E$12), ROW(E1)),””) here the IFERROR function gives an error if an error is found. Here no error is found.

Conclusion

They are the easiest methods of overcoming the problems of using the LARGE function with duplicates in Excel. We can find the nth largest value very easily with those simple methods. If you want to know more, please leave some comments below.

Joyanta Mitra

Joyanta Mitra

I am Joyanta Mitra. I graduated from BUET EEE in 2021. My college is Notre Dame College. My hobby is to play high graphics computer games. And I am going to pursue my career in your company Softeko. I am working and doing research on Microsoft Excel and here I will be posting articles related to this.

We will be happy to hear your thoughts

Leave a reply

5 Excel Hacks You Never Knew

Genius tips to help you unlock Excel's hidden features

FREE EMAIL BONUS

ExcelDemy
Logo