Lookup Top N Values Ignoring Duplicates in Excel

Excel is one of the best ways to organize and present a large amount of data. However, finding data in excel requires certain commands. In order to lookup top N values ignoring duplicates, it is important to first learn how to find top N values in Excel. Top N values mean finding the largest value, second largest value, and so on. However, if we want to ignore the Duplicate values, the ARRAY function comes into play, that is, the formula will change and ARRAY will be involved. Let’s now understand in detail with proper steps how to look up top N values ignore duplicates,

Lookup Top N Values without Ignoring Duplicates in Excel

Let’s first understand how to find the top N values, Suppose there is data for the marks of a student in 8 subjects, the marks are out of 100,

Step 1: In order to find the largest value, use formula,

=large(B1:B8,1)

Step 2: Now since 3 of the subjects have 96, if the second and third largest are found, they will be 96 only, which is a duplicate number.

Lookup Top N Values Ignoring Duplicates in Excel

Step 1: In order to find the Top N values ignoring duplicates in excel, use the ARRAY formula for the second-highest value without duplicate,

=ArrayFormula(large(if(B1:B8<F4,B1:B8),1))

Step 2: Now to repeat the same process, drag down the cell while keeping the values of B1 to B8 constant by adding $in from of them, $B$1, the formula shall look something like this,

=ArrayFormula(large(if($B$1:$B$8<F5,$B$1:$B$8),1))

In this way, top N values can be obtained while ignoring duplicates in excel.