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 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" 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 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" 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 <J...@discussions.microsoft.com> wrote:
> 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

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

"Vlad" wrote:

> On Mar 1, 9:23 pm, JMB <J...@discussions.microsoft.com> wrote:
> > 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
>
>
>
>

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