Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 --- |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Large Index Match Lookup | Excel Worksheet Functions | |||
Confused about arrays and ranges in functions | Excel Worksheet Functions | |||
How to sort/update large excel db | Excel Discussion (Misc queries) | |||
Match, Index, Vlookup, Large....Help Please | Excel Worksheet Functions | |||
Match, Index, Vlookup, Large....Help Please | Excel Worksheet Functions |