Exceljet

Quick, clean, and to the point

nth largest value with duplicates

Excel formula: nth largest value with duplicates
Generic formula 
=MAX(IF(rng<A2,rng))
Explanation 

To get the nth largest value in a data set with duplicates, you can use an array formula based on the MAX and IF functions.

Note: the LARGE function will easily return nth values, but LARGE will return duplicates when they exist in the source data.

In the example shown, the formula in E6 is:

=MAX(IF(rng<E5,rng))

Where "rng" is the named range B5:B11

How this formula works

First, we get the largest value using the MAX function in E5:

=MAX(rng)

Once we have the largest value established, we create another formula that simply checks all values in the named range "rng" against the "last largest value":

=MAX(IF(rng<E5,rng))

Note: this is an array formula and must be entered with control + shift + enter.

Any value that is lower than the "last largest" survives the test, and any value that's not ends up FALSE. The resulting array looks like this:

{12;FALSE;FALSE;11;12;12;10}

As the formula is copied down the column, E5 increments at each row, so that MAX and IF return a new array. The MAX function continues to return the largest value in each array — the nth value in the series.

It would be great to have an all in one formula that deals with n directly. If you have one, chime in below.

Author 
Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.