Rank and return Names

  • Thread starter Thread starter Karthik
  • Start date Start date
K

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.
 
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))
 
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.
 
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))
 
Back
Top