Find name matching max value

[baseball]

Say I have 100 records from Row 2 through Row 101

Col A -- Name
Cols B through AE -- data
Cols AF through AJ -- calculated ratios

I can generate lists of the top values in Col AF by
=LARGE(AF2:AF32,1)
=LARGE(AF2:AF32,2)
etc.

How can I return the names associated with =LARGE(AF2:AF32,n), so I get a listing like this, for example?

Name High
Anton 17.02
Baker 16.11
Smith 14.09

I tried using OFFSET(LARGE(AF2:AF32,1),0,-31) but I couldn't get it to work.

Thanks,
Cliff

[Aladin Akyurek]

On 2002-04-21 21:02, baseball wrote:
Say I have 100 records from Row 2 through Row 101

Col A -- Name
Cols B through AE -- data
Cols AF through AJ -- calculated ratios

I can generate lists of the top values in Col AF by
=LARGE(AF2:AF32,1)
=LARGE(AF2:AF32,2)
etc.

How can I return the names associated with =LARGE(AF2:AF32,n), so I get a listing like this, for example?

Name High
Anton 17.02
Baker 16.11
Smith 14.09

I tried using OFFSET(LARGE(AF2:AF32,1),0,-31) but I couldn't get it to work.

Thanks,
Cliff

In AK2 enter and copy down as far as needed:

=RANK(AF2,AF$2:AF$32)+COUNTIF(AF$2:AF2,AF2)-1

You can hide column AK if you want to.

In AL2 enter and copy down till AL11 rows:

=IF(ROW()-1<=10,INDEX(A$2:A$32,MATCH(ROW()-1,AK$2:AK$32,0)),"")

In AM2 enter and copy down till AM11 rows:

=IF(ROW()-1<=10,INDEX(AF$2:AF$32,MATCH(ROW()-1,AK$2:AK$32,0)),"")

Aladin

[baseball]

On 2002-04-21 22:35, Aladin Akyurek wrote:

In AK2 enter and copy down as far as needed:

=RANK(AF2,AF$2:AF$32)+COUNTIF(AF$2:AF2,AF2)-1

You can hide column AK if you want to.

In AL2 enter and copy down till AL11 rows:

=IF(ROW()-1<=10,INDEX(A$2:A$32,MATCH(ROW()-1,AK$2:AK$32,0)),"")

In AM2 enter and copy down till AM11 rows:

=IF(ROW()-1<=10,INDEX(AF$2:AF$32,MATCH(ROW()-1,AK$2:AK$32,0)),"")

Aladin

Thanks, Aladin. I can't say I understand why it works, but it does.

Cliff