1

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 gives 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?

I have Microsoft Excel 2007.

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

2 Answers 2

2

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.

2
  • This works, but it much more complicated that the answer below. Thanks for helping though!
    – The Snypr
    Dec 2, 2010 at 20:45
  • 1
    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, 2010 at 21:23
0

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.

3
  • 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, 2010 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, 2010 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, 2010 at 16:49

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .