excelexcel-formula

How to return a value based on another value

I have sample data as below:

Branch achievements1 point1
A 10 1
B 20 2
C 30 3
Branch achievements2 point2
A 30 3
B 20 2
C 10 1

So I need a table that point will be max of point1 and point2 then return respective achievements to this point. Below is my expected Output

Branch achievements point_max
A 30 3
B 20 2
C 30 3

What should I do in this case. Thank you.

Solution

  • Just to throw another option out there, this is pretty straightforward with index(match()) also.

    Presuming you have the data like this (and adjust as necessary if you don't, including across different sheets):

    3 tables of data, achievement group 1 in columns A/B/C, achievement group 2 in columns E/F/G, results in columns I/J/K. Each group is ordered as Branch/Achievement/Points

    Your max points column (k) would be:

    =IF(MAX(INDEX($C$2:$C$4,MATCH(I2,$A$2:$A$4,0)),INDEX($G$2:$G$4,MATCH(I2,$E$2:$E$4,0)))=INDEX($C$2:$C$4,MATCH(I2,$A$2:$A$4,0)),INDEX($C$2:$C$4,MATCH(I2,$A$2:$A$4,0)),INDEX($G$2:$G$4,MATCH(I2,$E$2:$E$4,0)))

    Your max achievements column (j) would be:

    =IF(MAX(INDEX($C$2:$C$4,MATCH(I2,$A$2:$A$4,0)),INDEX($G$2:$G$4,MATCH(I2,$E$2:$E$4,0)))=INDEX($C$2:$C$4,MATCH(I2,$A$2:$A$4,0)),INDEX($B$2:$B$4,MATCH(I2,$A$2:$A$4,0)),INDEX($F$2:$F$4,MATCH(I2,$E$2:$E$4,0)))