Rank and return Names

[Karthik]

Hi All,

I've a data from Col A to Col C, Column A contains names and column B
contains Quantily. I've used Rank condition in column C.

I want a formula to return names of top five in column F and quantity in
column G

A B C D E F

User 1 10 4 User 3 20
User 2 5 7 User 7 14
User 3 20 1 User 6 11
User 4 6 6 User 1 10
User 5 7 5 User 5 7
User 6 11 3
User 7 14 2
User 8 3 8

I can use Vlookup to get details in column F but would need help in finding
a formula to get Column E.

Thanks.

 

[Jacob Skaria]

In cell E1 and copy down
=INDEX($A$1:$A$10,MATCH(LARGE($B$1:$B$10,ROW(A1)),$B$1:$B$10,0))

In cell F1 and copy down
=LARGE($B$1:$B$10,ROW(A1))

 

[Karthik]

Thanks for your kind reply.

Formula works fine when all the ranks are different, but it doesn't return a
name if two users have same rank.
I'm not sure where I'm going wrong.
Please let me know how to get the names of top 5 Ranks.

I've used =Rank(A1,$A$1:$A$10) in column C.

 

[Jacob Skaria]

Hi Karthik

Try the below

In cell E1 and copy down. Please note that this is an array formula. You
create array formulas in the same way that you create other formulas, except
you press CTRL+SHIFT+ENTER to enter the formula. If successful in 'Formula
Bar' you can notice the curly braces at both ends like "{=<formula>}"

=INDEX($A$1:$A$10,SMALL(IF($B$1:$B$10=F1,ROW($B$1:$B$10)),
COUNTIF($F$1:F1,F1)))


In cell F1 (copy down as required)
=LARGE($B$1:$B$10,ROW(A1))

 

[Karthik]

Thanks Micky and Jacob.... Both methods worked pretty well...

Again Thanks a lot.....

 

[Bernd P]

Hello Karthi,

I suggest to use this approach:
http://sulprobil.com/html/sorting.html

Please see also
http://sulprobil.com/html/rank.html

Regards,
Bernd