Retrieving highest revenue, AND associated salesman

[LydiasDad]

I'm trying to ask a complicated question in a simple way. If you had the following table, how would you retrieve the salesman with the highest revenue? Note that I want the Revenue value AND the Salesman's name. Obviously, I could create a Pivot table with a max value on Revenue, but how to get the associated salesman as well?

Division Salesman Revenue
A Jones 100
A Smith 50
A Davis 250
A Samuels 600
A Shaugnessy 350
A Wildstein 425
B Feldman 50
B Bailey 275
B Potts 300
B Dawkins 400
B Measley 125

[AlphaFrog]

Here's one method.

A B C D E F 1 Division Salesman Revenue Max 2 A Jones 100 Salesman Revenue 3 A Smith 50 Samuels 600 4 A Davis 250 5 A Samuels 600 6 A Shaugnessy 350 7 A Wildstein 425 8 B Feldman 50 9 B Bailey 275 10 B Potts 300 11 B Dawkins 400 12 B Measley 125

Worksheet Formulas

Cell Formula E3 =INDEX(B:B,MATCH(MAX(C:C),C:C,0)) F3 =MAX(C:C)

[LydiasDad]

OK, that's good start. I should have specified that I'm doing this in a macro, and I also want the best salesman BY DIVISION.

Chris

[martindwilson]

in case of ties

	A	B	C	D	E	F
1	Division	Salesman	Revenue		Max
2	A	Jones	100		Salesman	Revenue
3	A	Smith	50		Davis	600
4	A	Davis	600		Samuels
5	A	Samuels	600		Feldman
6	A	Shaugnessy	350
7	A	Wildstein	425
8	B	Feldman	600
9	B	Bailey	275
10	B	Potts	300
11	B	Dawkins	400
12	B	Measley	125

	E	F
2	Salesman	Revenue
3	=IF(ROWS($A$1:A1)>COUNTIF($C$2:$C$25,MAX($C$2:$C$25)),"",INDEX($B$2:$B$25,MATCH(LARGE(INDEX(($C$2:$C$25=$F$3)*(1/ROW($A$2:$A$25)),0),ROWS($A$1:A1)),INDEX(($C$2:$C$25=$F$3)*(1/ROW($A$2:$A$25)),0),0)))	=MAX(C2:C25)
4	=IF(ROWS($A$1:A2)>COUNTIF($C$2:$C$25,MAX($C$2:$C$25)),"",INDEX($B$2:$B$25,MATCH(LARGE(INDEX(($C$2:$C$25=$F$3)*(1/ROW($A$2:$A$25)),0),ROWS($A$1:A2)),INDEX(($C$2:$C$25=$F$3)*(1/ROW($A$2:$A$25)),0),0)))
5	=IF(ROWS($A$1:A3)>COUNTIF($C$2:$C$25,MAX($C$2:$C$25)),"",INDEX($B$2:$B$25,MATCH(LARGE(INDEX(($C$2:$C$25=$F$3)*(1/ROW($A$2:$A$25)),0),ROWS($A$1:A3)),INDEX(($C$2:$C$25=$F$3)*(1/ROW($A$2:$A$25)),0),0)))