help on INDEX

[harshaputhraya]

Hi,
I am trying to use INDEX function to find the match for the maximum score in a set of columns (say O2:O25,Q2:Q25,S2:S25 )and return the corresponding name from column M.

I used the following formula
=INDEX(M2:M25,MATCH(MAX(O2:O25,Q2:Q25,S2:S25),O2:O25,0))
here it would return me the value only if the maximum score is present in
O2:O25

But i need to match the the maximum score with more than one columns.
ie i want to match it with O2:O25,Q2:Q25,S2:S25.

I tried out this formula
=INDEX(M2:M25,MATCH(MAX(O2:O25,Q2:Q25,S2:S25),O2:O25,Q2:Q25,S2:S25,0))

But its not executing.

Please provide me a solution.

Appreciate ur time n help

Harsha

[[email protected]]

Hello Harsha,

Quick & dirty:
=INDEX(M2:M25,MATCH(MAX(O2:O25,Q2:Q25,S2:S25),CHOOSE(1*(MAX(O2:O25)>=MAX(Q2:Q25,S2:S25))+2*(MAX(Q2:Q25)>=MAX(O2:O25,S2:S25))+4*(MAX(S2:S25)>=MAX(O2:O25,Q2:Q25)),O2:O25,Q2:Q25,O2:O25,S2:S25,O2:O25,Q2:Q25,O2:O25),))

HTH,
Bernd

[Domenic]

Here's another way...

U2:

=MAX(O2:O25,Q2:Q25,S2:S25)

V2:

=INDEX(M2:M25,MATCH(U2,INDEX(O2:S25,0,MATCH(TRUE,COUNTIF(OFFSET(O2:O25,,{
0,2,4}),U2)>0,0)*2-2+1),0))

Hope this helps!

In article
<[email protected]>,
harshaputhraya
<[email protected]> wrote:

> Hi,
> I am trying to use INDEX function to find the match for the maximum
> score in a set of columns (say O2:O25,Q2:Q25,S2:S25 )and return the
> corresponding name from column M.
>
> I used the following formula
> =INDEX(M2:M25,MATCH(MAX(O2:O25,Q2:Q25,S2:S25),O2:O25,0))
> here it would return me the value only if the maximum score is present
> in
> O2:O25
>
> But i need to match the the maximum score with more than one columns.
> ie i want to match it with O2:O25,Q2:Q25,S2:S25.
>
> I tried out this formula
> =INDEX(M2:M25,MATCH(MAX(O2:O25,Q2:Q25,S2:S25),O2:O25,Q2:Q25,S2:S25,0))
>
> But its not executing.
>
> Please provide me a solution.
>
> Appreciate ur time n help
>
> Harsha