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 the Excel **LARGE **function with duplicates in Excel.

**Table of Contents**hide

## How to Use Excel LARGE Function with Duplicates in Excel: 3 Ways

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.

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

- We have to write the formula in cell
**H5**.

`=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**.

- Finally, use the
**AutoFill**tool to copy the formula.

**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 cell**G5**.

**Read More: **How to Lookup Next Largest Value in Excel

### 2. Merging UNIQUE and LARGE Functions to Find Unique Largest Value

Here 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 value.

**Steps:**

- At first, we will type the formula in cell
**H5**.

`=LARGE(UNIQUE($E$5:$E$12),G5:G8)`

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

**Formula Breakdown**

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

**Read More: **How to Find Largest Number in Excel

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

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

**Steps:**

- We will use the formula in cell
**H5**.

`=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)),"")`

- Then, we will click on
**ENTER**.

- Apply the
**AutoFill**tool to get the final result.

** **

**Formula Breakdown**

**IF(E$5:E$12<>””,MATCH(E$5:E$12,E$5:E$12,0))**in this formula 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.

**Download Practice Workbook**

You may download the following workbook to practice yourself.

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

## Related Articles

- How to Use Excel
**LARGE**Function with Criteria - How to Use Excel
**LARGE**Function in Multiple Ranges - How to Use Excel
**Large**Function with Text - How to Find Second Largest Value with Criteria In Excel
- How to Use
**LARGE**and**SMALL**Function in Excel - How to Use
**LARGE**Function with**VLOOKUP**Function in Excel - How to Use
**VBA Large**Function in Excel