I have some data like:

A | B
a  12
b  12
c   0
d   5

How can I get the top 3 maximum values of column B from this list? The formula


gives me the following result:

a  12
a  12
d   5

However, the second a should be a b.

Any help is greatly appreciated.

  • Why would you downvote this.. I don't know how to make a nice lay-out in a post, and it's a good question
    – voluminat0
    Dec 18, 2015 at 13:41

1 Answer 1


You better try Excel AutoFilter or Filter for your purposes, it has "Top10" option.

Your sample is most probably shifting input ranges, when you copy it to next cells, be careful and use $ when needed.

This is how far i was able to get, but this is incomplete, because it must be filtered by already collected cells:

=INDEX(A$2:A$6; MATCH(LARGE(B$2:B$6; 1); B$2:B$6; 0))
=INDEX(A$2:A$6; MATCH(LARGE(B$2:B$6; 2); B$2:B$6; 0))
=INDEX(A$2:A$6; MATCH(LARGE(B$2:B$6; 3); B$2:B$6; 0))

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .