Index multiple cells
 

Original Question:

I need a function fo r the following:

In a given row of # values, I want to find the max. value and give the
associated text column heading in a new column. How do I do this??
Thanks

Original Answer:

With your headings in A1:Z1 and your numbers in A2:Z2
=INDEX(A1:Z1,MATCH(MAX(A2:Z2),A2:Z2),0)

This worked, but how can I reference more than one column heading
should there be multiple cells that equal the max value?

Thanks


--
xxxMarkxxx
------------------------------------------------------------------------
xxxMarkxxx's Profile: http://www.excelforum.com/member.php...o&userid=29401
View this thread: http://www.excelforum.com/showthread...hreadid=491169

Index multiple cells
 

A couple of ways:

=OFFSET(A1:z1,0,-1+MIN(IF(A1:z10=MAX(A1:z10),COLUMN(A1:z10))),1,1)
=INDEX(A1:z1,MIN(IF(A2:z10=MAX(A2:z10),COLUMN(A2:z 10))))

These are array formulas. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match--but you can't use the whole column.





xxxMarkxxx wrote:

Original Question:

I need a function fo r the following:

In a given row of # values, I want to find the max. value and give the
associated text column heading in a new column. How do I do this??
Thanks

Original Answer:

With your headings in A1:Z1 and your numbers in A2:Z2
=INDEX(A1:Z1,MATCH(MAX(A2:Z2),A2:Z2),0)

This worked, but how can I reference more than one column heading
should there be multiple cells that equal the max value?

Thanks

--
xxxMarkxxx
------------------------------------------------------------------------
xxxMarkxxx's Profile: http://www.excelforum.com/member.php...o&userid=29401
View this thread: http://www.excelforum.com/showthread...hreadid=491169

--

Dave Peterson