The Boss wants to know the names of the top 10 SalesReps

[James Perry]

Hello Friends...here's something i want2 share with you.

Assume i have 100 rows of data - a simple data set of SalesReps in column A and $Sales in column B.

The Boss wants2 know the names of the SalesReps who made it to the "top ten" in terms of $Sales value.

To find the name of the SalesRep who figured among the top ten -

drag the following formula down to TEN rows.

=INDEX(A:A,MATCH(LARGE(B:B,ROW(A1)),B:B))

 

[SirJB7]

Hi, James Perry!

Thanks for sharing this tip. And maybe you want to give a look at this from Chandoo a couple of days ago (once that he works... well, in fact he videorecorded it, there were another guys who actually worked... I was about to add "as usual" but I repented):

http://chandoo.org/forums/topic/sum-of-top-3-records-based-on-filtered-criteria#post-103195

Regards!

 

[Debraj]

Hi James Perry!


Thanks for sharing the tip...


In addition.. Just want to share something about MATCH ..


The default value for MATCH TYPE argument is 1.


To keep your BOSS happy for long time.. add a extra "0" for perfect match..

=INDEX(A:A,MATCH(LARGE(B:B,ROW(A1)),B:B),0)


Regards!

Deb

 

[TomK]

Hello All!
My first post, but I just wanted to say this is just the formula I needed! Thanks! I had been using a different approach using a helper column with rank(), then doing a vlookup on the rankings, but this cuts out the need for extra steps nicely!

Perhaps I am using the formula incorrectly, but I did notice that it doesn't seem to account for more than one instance of the same value (should two sales-people have the exact same sales!), instead listing the 1st instance twice? I worked around this by adding a rand() to the sales figures, ensuring they were all unique (with only negligible discrepancies) before applying the above formula, but perhaps someone has a tidier way to account for this?

Also, I think the 0 for 'exact match' needs to be within both sets of parentheses, i.e. =INDEX(A:A,MATCH(LARGE(B:B,ROW(A1)),B:B,0))