LARGE and INDEX functions

[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

 

[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)

 

[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)

 

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

 

[Max]

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))

 

[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?