Thanks:  0
Likes:  0

# Thread: Find name matching max value

1. 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

2. 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)),"")

3. On 2002-04-21 22:35, Aladin Akyurek wrote:
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)),"")

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

Cliff

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•