If there might be duplicates in the sales figures, you'll need to
create unique ranks. If you don't use unique ranks, several items will
have the same rank value and rank values will be skipped. You might
end up with ranks like, say, 1,2,2,2,5,6,7,7,7,10.. Clearly, this is
no good. This is why you need unique ranks.

For example, suppose you have salesman names in A1:A10 and sales
amounts in B1:B10. Insert a new column C and enter

=RANK(B1,B$1:B$10,0)+COUNTIF(B$1:B1,B1)-1

and fill down to B10. Make sure you use the $ characters in the
formula as shown. Otherwise, it won't work.

This will create unique ranks for the items in B1:B10. If there is a
tie, the rank is based on the position of the sales figures appear in
the list. For example, if both salesman X and Y have figures of 1000,
X will be ranked above Y if X's sales amount is in a lower numbered
row than Y's. There are ways to use subordinate tables to break ties,
but you don't seem to have such data in your model.

Once you have the unique ranks in C1:C10, enter the following formula
in D1 and fill down to D10:

=OFFSET(A$1,MATCH(ROW()-ROW(D$1)+1,C$1:C$10,0)-1,0,1,1)

This will return the names of the salesman in the order specified by
the ranks in C1:C10.

See www.cpearson.com/Excel/Rank.aspx for more details about ranking
data, including tie breaking.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)


On Sat, 18 Apr 2009 09:42:02 -0700, "Questor"
wrote:

I'm using the LARGE function to find the top five values (number) in column
"F" of a large list and place those values in Cells N1 thru N5 — Cell
N1=LARGE(F1:F100;1) thru Cell N5=LARGE(F1:F100;5).
Having found these values I want to associate them with the related text in
column "A" of that list so that I can correlate the sales figures with the
related salesmen (from column A) in cells M1 to M5. I used the LOOKUP
function to do that — M1=LOOKUP(N1;F1:F100;A1:A100) thru
M5=LOOKUP(N5;F1:F100;A1:A100).

I have two problems:
First - two salesmen have the same sales figures and the LARGE function
seems to arbitrarily distinguish them as the #1 and #2 top salesmen (although
I'm not really sure of that)
Second - the LOOKUP function is not returning expected results with two
variations
(a) cells M1 and M2 do not distinguish between top salesmen #1 and #2 and
returns #1 in both cases
(b) some returns from the list of salesmen do not correlate to their sales
totals

Can anyone help me sort this out?