79575628

Date: 2025-04-15 16:35:24
Score: 0.5
Natty:
Report link

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)))

Reasons:
  • Probably link only (1):
  • Long answer (-0.5):
  • Has code block (-0.5):
  • Low reputation (0.5):
Posted by: Jim