Find The Highest Value And Return Adjacent Cell Value with duplicates In Excel

Question

I am having an excel with below table

A B
Name Score
A 80
B 45
C 34
D 45
E 99
F 99
G 99

In the above table E, F and G's scores are same (sharing the maximum value).

I am trying to achieve the Top 3 'Names' and hence trying the formula -

Max 2nd =INDEX(A1:A8,MATCH(LARGE(B1:B8,2),B1:B8,1))

Max 3rd =INDEX(A1:A8,MATCH(LARGE(B1:B8,3),B1:B8,1))

However, I am getting the FIRST name's 'E' only for the rest of the maximum. values. I am expecting F and G to be displayed the next.

Can anyone please help.

Thanks in advance

Answer 1

You will need a list of the scores that you can increase the LARGE's k parameter using COUNTIF. This can be something as simple as the following in an unused cell and filled down,

=large(b$2:b$8, row(1:1))

Now use this formula beside the list you just created and fill down.

=INDEX(A:A, AGGREGATE(15, 7, ROW(A:A)/(B$1:B$8=E2), COUNTIF(E$2:E2, E2)))