Index/Match Help

[nymyth]

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

	A	B	C
1	Jan	Feb	Mar
2	30.2	40.2	60.0
3	5.4	15.1	90.1
4	30.1	40.5	50.3

<tbody>
</tbody>

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?

Jan	Feb	Mar	Result1	Result2
30,2	40,2	60,0	Feb	Mar
5,4	90,1	90,1
30,1	40,5	50,3

<tbody>
</tbody>

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