find value of a cell to the right a cell located with max()

  • Thread starter Thread starter 82716
  • Start date Start date
8

82716

row of data
4 colums
col 1 a number
col 2,"Boys"
col 3 a number
col 4 "Girls"
Want to find the max() on the row and contents of the next cell, same row
(ie: "Boys" or "Girls")...
 
I assume that your data is look like this…

A (Col 1 ) B (Col 2) C (Col 3) D (Col 4)
20 Boys 12 Girls
16 Boys 20 Girls

Now in E1 cell paste this formula
=IF(A1>C1,A1&" "&B1,C1&" "&D1)

Copy the E1 cell formula and paste it to the remaining cells.

Change the cell reference to your desired cell, if required.

If this post helps, Click Yes!
 
If you have a lot of these kind of alternating* source data cols
to compare in rows 2/3
*num-textlabel-num-textlabel-num-textlabel-num-textlabel ....

you could use this
In say, A5: =INDEX(2:2,MATCH(MAX(2:2),2:2,0)+1)
Copy down to A6

And to cover the possibility of ties in the maximums,
you could use this in A5:
=IF(COUNTIF(2:2,MAX(2:2))>1,">1 Max",INDEX(2:2,MATCH(MAX(2:2),2:2,0)+1))
Adapt the ties trap return to suit: ">1 Max"

Success? hit the YES below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
 
Back
Top