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

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!

-
Maybe add a screenshot to your question with some sample data (and show the desired result) – Siim K Dec 2 '10 at 19:10

## 2 Answers

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.

-
This works, but it much more complicated that the answer below. Thanks for helping though! – The Snypr Dec 2 '10 at 20:45
fair enough. Mehper's answer is short and the better for a small group of data (as you have). But if you should run into a situation where you have many rows of data mine would scale more easily. – Xantec Dec 2 '10 at 21:23

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.

-
This is PERFECT! Thanks a bunch for helping. – The Snypr Dec 2 '10 at 20:44
New, resulting problem: Normally Excel only applies absolute references to whatever value is directly after the \$, but use of this formula for some reason applies it to the specific cell, and not just the column (which is what I want). Is there any workaround? – The Snypr Dec 2 '10 at 20:59
@Snypr - the \$ absolute reference works two ways: \$A\$4 will always reference the cell A4. \$A4 will always reference column A but will vary by row number. A\$4 will always reference row 4 but will vary by column. – JDB Dec 2 '10 at 22:24
I know this, but for some reason Excel takes \$A4 as \$A\$4 in he formula. Here are three formulas, copied/pasted: =IF(\$S\$4>\$S12,\$D\$69,M\$69), =IF(\$S\$4>\$S12,\$D\$69,N\$69), =IF(\$S\$4>\$S12,\$D\$69,O\$69). Note what changes. S12 needs to be S13, S14, etc. – The Snypr Dec 3 '10 at 16:49