LARGE() function and rows with same rank

A finesse you can apply with the LARGE() function is to consider the case where you have rows with the same value, e.g:
1 James
3 Fred
2 Ian
1 Graham
3 Nigel

The LARGE(A1:A5,1) function will simply tell you that 3 is the largest value. But you may actually want the top three rows, e.g. to return the set (Fred, Nigel, Ian)

The way to achieve this is to add some unique key value to each ranking value so that they become distinct. In the example above, we could create a new column with value as follows:

This will then give us:
1 James 1.000001
3 Fred 3.000002
2 Ian 2.000003
1 Graham 1.000004
3 Nigel 3.000005

If we run the LARGE() function against Column C, we now have our unique values, which we can then feed into a VLOOKUP to return the person’s name.

Leave a Reply

Your email address will not be published. Required fields are marked *