finding highest dollar amount
 

I would like a formula that will draw out of a list of sales people and their
sales the top sale and who that person is:

For example if these are my sales:

Brian 72.9
Brian 104.77
Brian 119.99
Brian 229.97
Brian 142.84
Brian 259.87
Cassi 301.95
Cassi 62.9
Cassi 49.95
Cassi 139
Chad 94.99
Chad 76.9
Chris 283.98
Chris 34.95
Chris 133.97
Chris 249.99
Chris 1499.87
Clarissa 98.85
Clarissa 125.95
Clarissa 62.9


I would like the formula to draw out Chris in one cell and $1499.87 in the
cell next to his name.

Thanks for your help.

Your data in A2:B200

In cell E2 =MAX(B2:B200)
In cell D2 =INDEX(A2:A200,MATCH(E2,B2:B200,0))

In case of a tie you only get the first one listed though.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------



"Jim Gentile" wrote in message
...
I would like a formula that will draw out of a list of sales people and their
sales the top sale and who that person is:

For example if these are my sales:

Brian 72.9
Brian 104.77
Brian 119.99
Brian 229.97
Brian 142.84
Brian 259.87
Cassi 301.95
Cassi 62.9
Cassi 49.95
Cassi 139
Chad 94.99
Chad 76.9
Chris 283.98
Chris 34.95
Chris 133.97
Chris 249.99
Chris 1499.87
Clarissa 98.85
Clarissa 125.95
Clarissa 62.9


I would like the formula to draw out Chris in one cell and $1499.87 in the
cell next to his name.

Thanks for your help.



---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.806 / Virus Database: 548 - Release Date: 05/12/2004

=INDEX(A2:A20,MATCH(MAX(B2:B20),B2:B20,0))


Regards,

Peo Sjoblom

"Jim Gentile" wrote:

I would like a formula that will draw out of a list of sales people and their
sales the top sale and who that person is:

For example if these are my sales:

Brian 72.9
Brian 104.77
Brian 119.99
Brian 229.97
Brian 142.84
Brian 259.87
Cassi 301.95
Cassi 62.9
Cassi 49.95
Cassi 139
Chad 94.99
Chad 76.9
Chris 283.98
Chris 34.95
Chris 133.97
Chris 249.99
Chris 1499.87
Clarissa 98.85
Clarissa 125.95
Clarissa 62.9


I would like the formula to draw out Chris in one cell and $1499.87 in the
cell next to his name.

Thanks for your help.