Can't get INDEX MACTCH to work

[Chris]

Hello Experts-

A B C D E F
10 21 55 23 22 16
2 56 87 54 21 32
18 17 71 88 99 1
23 44 64 100 72 91

With this data set, I'm trying to find the position of the
max value. I want to return the column header, in this
case, D. This is the formula I have tried-

=INDEX(D2:I2,1,MATCH(MAX(D3:I6),D3:I6,0))

It seems as though MATCH does not work in a two dimensonal
range. I've looked through help but there is no mention of
this and all the examples use a single row/column as the
LOOKUP_ARRAY. I've also tried entering this as an array
formula. I get the #NA error either way.

What's the correct method for this situation?

TIA
Chris.

 

[Aladin Akyurek]

=INDEX(D2:I2,MAX((MAX(D3:I6)=D3:I6)*COLUMN(D3:I6))-CELL("Col",$D$2)+1)

which must be confirmed with control+shift+enter instead of just with enter.

Caveat. In case of multiple instances of the maximum, you'll get the header
that corresponds to the last instance.

 

[Chris]

Thank you Aladin. That ends up to be a little more complex
than I realized! Works fine.

Chris.