simple MAX + comparison formula

[j0e]

hi

ok here's my question. i have 4 columns (A). for each row, i need the
5th cell to say which is the highest. lets say a-d are apples, oranges,
bananas, pears. and the spread looked like this:

A O B P
5 7 3 5
1 8 2 4
1 0 6 4
8 2 6 3

i would want to add a 5th column that figures out which of A-D is
highest and tells me. so it would look like this:

A O B P Max
5 7 3 5 Oranges
1 8 2 4 Oranges
1 0 6 4 Bananas
8 2 6 3 Apples

i'm not worried about ties (they will be very rare) so you can have an
error or give the wrong answer, whatever.

thanks very much for your help

 

[duane]

your data in a4:d8

this in cell e5 and copied down
through e8

=OFFSET($A$4,0,MATCH(MAX(A55),A55,0)-1,1,1)

 

[Barb Reinhardt]

If you have 5,7,3,5 in row 2 column A-D, enter the following in E2.

=INDIRECT("R1C"&MATCH(MAX(A22),A22,0),FALSE)

 

[R.VENKATARAMAN]

suppose your data including column headings are in A1 to D5

type this in E2
=OFFSET($A$1,0,MATCH(MAX(A22),A22)-1,1,1)
copy to E3 to E5
your will get
O
O
B
P

 

[R.VENKATARAMAN]

mistake in my formula

it should be
=OFFSET($A$1,0,MATCH(MAX(A22),A22,0)-1,1,1)----------0 to be added at
the match function
apologies

suppose your data including column headings are in A1 to D5

type this in E2
=OFFSET($A$1,0,MATCH(MAX(A22),A22)-1,1,1)
copy to E3 to E5
your will get
O
O
B
P

 

[j0e]

thank you very much, got it to work with your formula