Can't get INDEX MACTCH to work

  • Thread starter Thread starter Chris
  • Start date Start date
C

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.
 
=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.
 
Thank you Aladin. That ends up to be a little more complex
than I realized! Works fine.

Chris.
 
Back
Top