LARGE and INDEX functions
 

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:$E10048,PlayerSummar y!$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(Pla yerSummary!$E$2:$E100=48,PlayerSummary!$AB$2:$AB$ 100),D7),PlayerSummary!$E$2:$E100,0))

Can anyone help?

Thanks,
Bob

LARGE and INDEX functions
 

You have one of the ranges wrong, change to

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


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=529035

LARGE and INDEX functions
 

So why not

=MAX(IF(PlayerSummary!$E$2:$E10048,PlayerSummary! $E$2:$E$100))


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"bob" wrote in message
...
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:$E10048,PlayerSummar y!$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(Pla yerSummary!$E$2:$E100=48,
PlayerSummary!$AB$2:$AB$100),D7),PlayerSummary!$E$ 2:$E100,0))

Can anyone help?

Thanks,
Bob

LARGE and INDEX functions
 

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.

"Bob Phillips" wrote:

So why not

=MAX(IF(PlayerSummary!$E$2:$E10048,PlayerSummary! $E$2:$E$100))


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"bob" wrote in message
...
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:$E10048,PlayerSummar y!$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(Pla yerSummary!$E$2:$E100=48,
PlayerSummary!$AB$2:$AB$100),D7),PlayerSummary!$E$ 2:$E100,0))

Can anyone help?

Thanks,
Bob

LARGE and INDEX functions
 

"bob" wrote:
"Bob Phillips" wrote:
=MAX(IF(PlayerSummary!$E$2:$E10048,
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(Play erSummary!$AB$2:$AB10048,
PlayerSummary!$AB$2:$AB$100)),PlayerSummary!$AB$2: $AB100,0))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

LARGE and INDEX functions
 

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:$E10048,PlayerSumma ry!$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?