Showing the Winner

[Kevin]

What function analizes a row or column of numbers and
returns the corresponding name for the highest (or lowest)
value. Like the name of the salesperson who sold the
highest $ of furniture.

 

[Max]

One way (assuming that there are *no* ties involved):

Suppose Names & $Sales are in cols A and B, row 2 downwards

Put in D2: =OFFSET($A$1,MATCH(E2,B:B,0)-1,0,1,1)
Put in E2: =LARGE(B:B,ROW()-1)

D2:E2 returns the top salesperson and the $Sales

If you want say, the top 5:
select D2:E2, copy down to row 6

D2:E6 will return the Top 5 salespersons and their $Sales

And if you want the lowest (instead of highest),
just replace the formula in E2 by: =SMALL(B:B,ROW()-1)

--
hth
Max
-----------------------------------------
Please reply in newsgroup

Use xdemechanik
<at>yahoo<dot>com
for email

 

[Peo Sjoblom]

Sales in B2:B50, names in A2:A50

=INDEX($A$2:$A$50,MATCH(MAX($B$2:$B$50),$B$2:$B$50,0))


if there can be multiple max then you are better of using advanced or
autofilter

 

[Jim]

This array formula (Ctrl+Enter instead of Enter alone) finds the MAX
restaurant...high sales..., comparing four restaurants and three meals:

{=INDEX(A1:A5,MAX((B25=MAX(B25))*(ROW(B25))))}
A1="Restaurants"
A2:A5=Restaurant Names
B25=Amounts

This array formula returns the MAX meal in the scenario above:

{=INDEX(A11,MAX((B25=MAX(B25))*(COLUMN(B25))))}

A1="Restaurants"
B1="Breakfast"
C1="Lunch"
D1="Dinner"

Once you set up the scenario and try the formula(s), you can adapt it to
your particular situation.

All the best,
Jim



(The curly brackets are inserted by XL when entering the array formula.)