Index multiple cells

X

xxxMarkxxx

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
 
D

Dave Peterson

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:z10))))

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top