Reputation: 1695
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...
Upvotes: 1
Views: 86
Reputation: 152605
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.
Upvotes: 3