How to get Excel MAX function to give text of cell adjacent to winning cell?

Question

Can anyone suggest a way to make Excel find the maximum value of two cells, then, depending on which is larger, give the text contained by another cell in the MAX cell's row? Currently I have "=(MAX($S$4,$S5))" which only give the value of the larger cell, but I want it to give the text in R4 if S4 is larger than S5, and give the text in R5 if S is larger that S4. Is there a way to do this?

Thank you!

Answer 1

This will do the trick. it is set up to work with your example ranges

=INDIRECT(CONCATENATE("R",MATCH(MAX(S4:S5),S4:S5,0)+3))

the +3 at the end off sets the value returned by the MATCH function to get to the desired rows. MATCH() will return the location of a value (in this case the MAX of S4 and S5) in an array of values (in this case the values listed in S4 and S5). so if S4 is the larger then MATCH would return a 1 (the first value) and if S5 were larger then MATCH would return 2 (the second value in the array). therefore to reach rows 4 or 5 just add 3, which i did.

Answer 2

You can use nested IF clauses.

=IF(S4>S5,R4,IF(S5>S4,R5))

or more simply,

=IF(S4>S5,R4,R5)

Note that the above formula gives R4 if S4 is greater than S5, and otherwise it gives R5. This means that if S4 = S5, the result will be R5.