LARGE and INDEX functions

B

bob

I have entered a formula in the current worksheet that provides the highest
value in a column in another worksheet based on whether values in a second
column are greater than 48.

=LARGE(IF(PlayerSummary!$E$2:$E100>48,PlayerSummary!$AB$2:$AB$100),1)

Now, I want a formula in the current worksheet to return a corresponding
value from that same row (different column) in the PlayerSummary worksheet.
The value I want to return is in column E.

I tried this, but it returned a #NUM! value:

=INDEX(PlayerSummary!$E$2:$E100,MATCH(LARGE(IF(PlayerSummary!$E$2:$E100>=48,PlayerSummary!$AB$2:$AB$100),D7),PlayerSummary!$E$2:$E100,0))

Can anyone help?

Thanks,
Bob
 
D

daddylonglegs

You have one of the ranges wrong, change to

=INDEX(PlayerSummary!$E$2:$E100,MATCH(LARGE(IF(PlayerSummary!$E$2:$E100>=48,PlayerSummary!$AB$2:$AB$100),D7),PlayerSummary!*$AB$2:$AB$100*,0)
 
B

Bob Phillips

So why not

=MAX(IF(PlayerSummary!$E$2:$E100>48,PlayerSummary!$E$2:$E$100))


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
B

bob

This produces the highest value in column E. I want it to produce the value
in column that is in the same row as the highest value in column AB.

Any thoughts?

Thanks.
 
M

Max

bob said:
PlayerSummary!$E$2:$E$100))

This produces the highest value in column E.
I want it to produce the value in column
that is in the same row as the highest value in column AB.

Assuming there'll be no ties in the max values
we could try, array-entered (press CTRL+SHIFT+ENTER):
=INDEX(PlayerSummary!$E$2:$E$100,MATCH(MAX(IF(PlayerSummary!$AB$2:$AB100>48,
PlayerSummary!$AB$2:$AB$100)),PlayerSummary!$AB$2:$AB100,0))
 
H

Harlan Grove

bob wrote...
I have entered a formula in the current worksheet that provides the highest
value in a column in another worksheet based on whether values in a second
column are greater than 48.

=LARGE(IF(PlayerSummary!$E$2:$E100>48,PlayerSummary!$AB$2:$AB$100),1)

Now, I want a formula in the current worksheet to return a corresponding
value from that same row (different column) in the PlayerSummary worksheet.
The value I want to return is in column E.

I tried this, but it returned a #NUM! value:

=INDEX(PlayerSummary!$E$2:$E100,
MATCH(LARGE(IF(PlayerSummary!$E$2:$E100>=48,
PlayerSummary!$AB$2:$AB$100),D7),PlayerSummary!$E$2:$E100,0))

The IF call returns values in column AB, so the LARGE call returns a
value in column AB as well, but the MATCH call is trying to find the
column AB value in column E. Is there any reason to believe the largest
column AB value will also appear in column E? I suspect this is a bug,
and you really want

=INDEX(PlayerSummary!$E$2:$E100,
MATCH(LARGE(IF(PlayerSummary!$E$2:$E100>=48,
PlayerSummary!$AB$2:$AB$100),D7),PlayerSummary!$AB$2:$AB$100,0))

If there were no values in column E >= 48, this would also return
#NUM!. If there were no values in column E >= 48, what result do you
want?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top