CODEWITHSUNDEEP
excelidentity-column
Walrus
Walrus

Reputation: 20444

Excel - Tell me the column header that had the highest number for each row

| Name  | Blues | Greens | Yellows | Reds | Winner |
| Smith | 35    | 42     | 52      | 17   |        |

I want to know the column header that has the largest number for each row and insert its name in the 'Winner' column.

How would this be done

Upvotes: 3

Views: 12745

Answers (2)

576i
576i

Reputation: 8362

Assuming the Rows are "A" for the Name, "B" - "E" for the values and "F" for the winner, this works.

=IF(MAX(B3:E3)=B3;B$1;IF(MAX(B3:E3)=C3;C$1;IF(MAX(B3:E3)=D3;D$1;E$1)))

Upvotes: 0

kb_sou
kb_sou

Reputation: 1057

You can do this by using a formula like (suposing the headers on A1:E1)

=OFFSET($A$1,0,MATCH(MAX($A2:$E2),$A2:$E2,0)-1)

The Match function will give the position of the maximum value of the range $A2:$E2. Then you can use theOffset function to get the header on that position

Upvotes: 7

Related Questions