Assuming that A2:B11 contains your data, try the following...

C2, copied down:

=RANK(A2,$A$2:$A$11)+COUNTIF($A$2:A2,A2)-1

D1: enter 5, indicating that you want a Top 5 list

*Change this number according to the desired Top N list

E1:

=MAX(IF(A2:A11=INDEX(A2:A11,MATCH(D1,C2:C11,0)),C2 :C11))-D1

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER

F2, copied down and across:

=IF(ROWS(F$2:F2)<=$D$1+$E$1,INDEX(A$2:A$11,MATCH(R OWS(F$2:F2),$C$2:$C$11,
0)),"")

Hope this helps!


In article ,
ufo_pilot wrote:

I've tried several different ways to solve this problem:
I would like to list the names with the 5 highest scores
some are tied for position.
all the formulae I've tried does not result in what I expected
Where am I going wrong??


col A col B
1 John
1 Mary
2 Sue
3 Bob
3 Jester
5 Sam
15 Tina
10 Rex
10 Lowell
4 Grinch

=VLOOKUP(LARGE($A$12:$A$100,ROW(1:1)),$A$12:$B$100 ,1,FALSE)
=VLOOKUP(LARGE($A$12:$A$100,ROW(5:5)),$A$12:$B$100 ,2,FALSE)
15 Tina
10 Rex
10 Rex
5 Sam
4 Grinch
3 Bob
3 Bob
2 Sue
1 John
1 John

gives me duplicates of same number in col A ( will not find next number down
- eliminates Mary, Jester and Lowell)

=VLOOKUP(MAX(A:A),A:B,2,FALSE)&" "&MAX(A:A)
gives me only the MAX with the name

I have even used CONCATENATE to solve it, but that did not work either

what to do???
Thanks for any help you can offer.