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

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.

Formula Breakdown

• UNIQUE($E$5:$E$12) finds out the unique value from selected cells of column E.
• =LARGE(UNIQUE($E$5:$E$12),G5:G8) returns the large function in column G.

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) frequency returns 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)) 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)),””) the IFERROR function gives an error if an error is found. Here no error is found.