Array Formula - Offset Result Problem
 

The following Array formula retulrns the maximum value in column E
where the value in Column H is Blue

{=MAX(($E$2:$E$115)*($H$2:$H$115="Blue"))}

How would I return the text in Column B for which is on the same line
as the maximum value in column E where the value in Column H is Blue

I'm using Excel 2003

TIA

A

Array Formula - Offset Result Problem
 

One way
=INDEX($B$2:$B$115,MATCH(MAX(($E$2:$E$115)*($H$2:$ H$115="Blue"))&"Blue",$E$2:$E$115&$H$2:$H$115,0))

array entered

"Vlad" wrote:

The following Array formula retulrns the maximum value in column E
where the value in Column H is Blue

{=MAX(($E$2:$E$115)*($H$2:$H$115="Blue"))}

How would I return the text in Column B for which is on the same line
as the maximum value in column E where the value in Column H is Blue

I'm using Excel 2003

TIA

A

Array Formula - Offset Result Problem
 

Vlad,

It's still an array

=INDEX($B$2:$B$115,MATCH(MAX(($E$2:$E$115)*($H$2:$ H$115="Blue")),$E$2:$E$115,FALSE),1)

Mike

"Vlad" wrote:

The following Array formula retulrns the maximum value in column E
where the value in Column H is Blue

{=MAX(($E$2:$E$115)*($H$2:$H$115="Blue"))}

How would I return the text in Column B for which is on the same line
as the maximum value in column E where the value in Column H is Blue

I'm using Excel 2003

TIA

A

Array Formula - Offset Result Problem
 

you would get an incorrect result if the data we

2 red
5 red
5 blue
4 green

where another color happens to have a number that equals blues max and
appears before blue in the list.



"Mike H" wrote:

Vlad,

It's still an array

=INDEX($B$2:$B$115,MATCH(MAX(($E$2:$E$115)*($H$2:$ H$115="Blue")),$E$2:$E$115,FALSE),1)

Mike

"Vlad" wrote:

The following Array formula retulrns the maximum value in column E
where the value in Column H is Blue

{=MAX(($E$2:$E$115)*($H$2:$H$115="Blue"))}

How would I return the text in Column B for which is on the same line
as the maximum value in column E where the value in Column H is Blue

I'm using Excel 2003

TIA

A

Array Formula - Offset Result Problem
 

Your right, forgot about duplicates, thanks for the correction

Mike


"JMB" wrote:

you would get an incorrect result if the data we

2 red
5 red
5 blue
4 green

where another color happens to have a number that equals blues max and
appears before blue in the list.



"Mike H" wrote:

Vlad,

It's still an array

=INDEX($B$2:$B$115,MATCH(MAX(($E$2:$E$115)*($H$2:$ H$115="Blue")),$E$2:$E$115,FALSE),1)

Mike

"Vlad" wrote:

The following Array formula retulrns the maximum value in column E
where the value in Column H is Blue

{=MAX(($E$2:$E$115)*($H$2:$H$115="Blue"))}

How would I return the text in Column B for which is on the same line
as the maximum value in column E where the value in Column H is Blue

I'm using Excel 2003

TIA

A

Array Formula - Offset Result Problem
 

On Mar 1, 9:23*pm, JMB wrote:
you would get an incorrect result if the data we

2 * *red
5 * *red
5 * *blue
4 * *green

where another color happens to have a number that equals blues max and
appears before blue in the list.


Thanks for the suggestion but they don't work for me as it is quite
likely that there would be duplicate items with the same max value.

I thought something like {=MAX(($E$2:$E$115)*($H$2:$H$115=$J4)*($B$2:$B
$115))} would work but it doesn't.

Any other suggestions?

TIA

Array Formula - Offset Result Problem
 

why did my first suggestion not work?????

"Vlad" wrote:

On Mar 1, 9:23 pm, JMB wrote:
you would get an incorrect result if the data we

2 red
5 red
5 blue
4 green

where another color happens to have a number that equals blues max and
appears before blue in the list.


Thanks for the suggestion but they don't work for me as it is quite
likely that there would be duplicate items with the same max value.

I thought something like {=MAX(($E$2:$E$115)*($H$2:$H$115=$J4)*($B$2:$B
$115))} would work but it doesn't.

Any other suggestions?

TIA

Array Formula - Offset Result Problem
 

I put the following data into a test sheet:-

1 COL B COL E COL H
2 Test 1 2 red
3 Test 2 5 red
4 Test 3 5 blue
5 Test 4 4 green

When I put the array forumla =INDEX($B$2:$B$115,MATCH(MAX(($E$2:$E
$115)*($H$2:$H$115="Blue")),$E$2:$E$115,FALSE),1) into cell L2 it
returns the test Test 2 whereas I was expecting Test 3.

I have uploaded the file to http://www.mediafire.com/?vglvvl3jtn0 -
not sure whether you would be happy to open it though but it's there
if you want.

TIA

Andy

Array Formula - Offset Result Problem
 

that was not my suggestion. my suggestion was

=INDEX($B$2:$B$115,MATCH(MAX(($E$2:$E$115)*($H$2:$ H$115="Blue"))&"Blue",$E$2:$E$115&$H$2:$H$115,0))

array entered - which returns Test 3 for me.



"Vlad" wrote:

I put the following data into a test sheet:-

1 COL B COL E COL H
2 Test 1 2 red
3 Test 2 5 red
4 Test 3 5 blue
5 Test 4 4 green

When I put the array forumla =INDEX($B$2:$B$115,MATCH(MAX(($E$2:$E
$115)*($H$2:$H$115="Blue")),$E$2:$E$115,FALSE),1) into cell L2 it
returns the test Test 2 whereas I was expecting Test 3.

I have uploaded the file to http://www.mediafire.com/?vglvvl3jtn0 -
not sure whether you would be happy to open it though but it's there
if you want.

TIA

Andy

Array Formula - Offset Result Problem
 

That works like a treat - thanks for both of your help and suggestions