Lookup column header based on max value

Question

I have a case where there are multiple values corresponding to an id. I need to get the value having highest value and also the heading of the value column.

For Example:

id    a   b   c   d   e   f   g
12    6   0   7   0   1   0   0
14    0   1   0   0   2   4   0
16    5   0   0   9   0   0   2
18    0   2   5   0   0   1   3

Expected Result:

id    a   b   c   d   e   f   g  max   col
12    6   0   7   0   1   0   0    7     c
14    0   1   0   0   2   4   0    4     f
16    5   0   0   9   0   0   2    9     d
18    0   2   5   0   0   1   3    5     c

I can get the 'max' column by using the max() fn. But how can i obtain the values for 'col' which gives me the heading corresponding to the max value in the table?

Please help!

Thanks...

Answer 1

To find the Max:

=MAX(B2:H2)

to find the column:

=INDEX($1:$1,MATCH(I2,2:2,0))

Put these in there corresponding columns in row 2 and copy down.