Array Formula - Offset Result Problem

[Vlad]

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

 

[JMB]

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

 

[Mike H]

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

 

[JMB]

you would get an incorrect result if the data were:

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]

Your right, forgot about duplicates, thanks for the correction

Mike

 

[Vlad]

you would get an incorrect result if the data were:

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

 

[JMB]

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

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

 

[Vlad]

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

 

[JMB]

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]

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