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

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

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

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

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

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

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

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

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

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