Index/Match Help

[nymyth]

Hi all, I need some help regarding Index/Match as I'm not sure why this isn't working.

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Jan[/TD]
[TD]Feb[/TD]
[TD]Mar[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]30.2[/TD]
[TD]40.2[/TD]
[TD]60.0[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]5.4[/TD]
[TD]15.1[/TD]
[TD]90.1[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]30.1[/TD]
[TD]40.5[/TD]
[TD]50.3[/TD]
[/TR]
</tbody>[/TABLE]

in the table above, I want to output which Month has the largest value (so in this case, my output should read Mar)
this is my current formula =index(A1:C1,Match(large(A1:C4,1),A2:C4,1)) - however this gives me an error. If I limit my array to A3:C3 it works fine. Any help is appreciated.

Thanks.

 

[markmzz]

Try the Array Formula below:

In D2 - use Ctrl+Shift+Enter to enter the formula

=INDEX($A$1:$C$1,SMALL(IF($A$2:$C$4=MAX($A$2:$C$4),COLUMN($A$1:$C$1)-COLUMN($A$1)+1),1))

Markmzz

 

[nymyth]

Thank you Markmzz - so why do I have to do it via an array? Also, what if I wanted in a different cell to show the 2nd largest and in another cell the 3rd largest?

 

[markmzz]

Thank you Markmzz - so why do I have to do it via an array? Also, what if I wanted in a different cell to show the 2nd largest and in another cell the 3rd largest?

In this case, try this:

In D2 - use Ctrl+Shift+Enter to enter the formula

=INDEX($A$1:$C$1,SMALL(IF($A$2:$C$4-COLUMN($A$1:$C$1)/10^6=LARGE($A$2:$C$4-COLUMN($A$1:$C$1)/10^6,1),COLUMN($A$1:$C$1)-COLUMN($A$1)+1),1))
​

Markmzz

 

[nymyth]

Thanks, went an easier route. Essentially in D2 I added =large (A2:C2,1), then in D3 I did =INDEX($A$1:$C$1,SMALL(IF($A$2:$C$4=$D$2,COLUMN($A$1:$C$1)-COLUMN($A$1)+1),1))

Then in E2 I added =large(A2:C2,2) and in E3 =INDEX($A$1:$C$1,SMALL(IF($A$2:$C$4=$E$2,COLUMN($A$1:$C$1)-COLUMN($A$1)+1),1))

This did the trick, so thanks again.

 

[markmzz]

Thanks, went an easier route. Essentially in D2 I added =large (A2:C2,1), then in D3 I did =INDEX($A$1:$C$1,SMALL(IF($A$2:$C$4=$D$2,COLUMN($A$1:$C$1)-COLUMN($A$1)+1),1))

Then in E2 I added =large(A2:C2,2) and in E3=INDEX($A$1:$C$1,SMALL(IF($A$2:$C$4=$E$2,COLUMN($A$1:$C$1)-COLUMN($A$1)+1),1))

This did the trick, so thanks again.

And if you have this?

[TABLE="class: grid, width: 320"]
<tbody>[TR]
[TD="width: 64, bgcolor: transparent, align: center"]Jan[/TD]
[TD="width: 64, bgcolor: transparent, align: center"]Feb[/TD]
[TD="width: 64, bgcolor: transparent, align: center"]Mar[/TD]
[TD="width: 64, bgcolor: transparent, align: center"]Result1[/TD]
[TD="width: 64, bgcolor: transparent, align: center"]Result2[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: center"]30,2[/TD]
[TD="bgcolor: transparent, align: center"]40,2[/TD]
[TD="bgcolor: transparent, align: center"]60,0[/TD]
[TD="bgcolor: transparent, align: center"]Feb[/TD]
[TD="bgcolor: transparent, align: center"]Mar[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: center"]5,4[/TD]
[TD="bgcolor: transparent, align: center"]90,1[/TD]
[TD="bgcolor: transparent, align: center"]90,1[/TD]
[TD="bgcolor: transparent, align: center"]
[/TD]
[TD="bgcolor: transparent, align: center"]
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: center"]30,1[/TD]
[TD="bgcolor: transparent, align: center"]40,5[/TD]
[TD="bgcolor: transparent, align: center"]50,3[/TD]
[TD="bgcolor: transparent, align: center"]
[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]

Markmzz

 

[nymyth]

haha, fair enough (didnt consider that) but the size of the numbers are 9 digits and there is not a chance that 2 numbers will end up alike. I have kept your copy of your formula so I can try to break it down and understand it. Thanks.

 

[markmzz]

haha, fair enough (didnt consider that) but the size of the numbers are 9 digits and there is not a chance that 2 numbers will end up alike. I have kept your copy of your formula so I can try to break it down and understand it. Thanks.

You are welcome and thanks for the return.

Markmzz