How to return rows with top five values in a certain column

Question

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

=INDEX(A2:A6;Match(MAX(B2:B6);B2:B6;1))

gives me the following result:

a  12
a  12
d   5

However, the second a should be a b.

Any help is greatly appreciated.

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))