Forum Discussion
Complicated lookup involving multiple Max values and secondary conditions - WORLD CUP EDITION
I'm building a spreadsheet to track and bracket the World Cup. Anyone familiar with the rules knows that there are multiple logical criteria for determining who progresses. Reference this screengrab:...
So I'm not sure I know the rules but do know the PTS then GD part as you described. I don't know what happens next. That said here is a trick I can recommend. Instead of trying to do IF this then that but if that then this and .... You can just incorporate all the tie breakers into a single formula. For example with the PTS and GD you can check for MAX( 10*PTS + GD). So adding a third condition might be MAX(100*PTS+10*GD+PF) you just have to make sure that you scale each 'tier' to always be greater than the tier below. Although I don't think GD tie is possible for 2 potential winning teams to be 0 or negative you could use 10*(GD+10) if that is a concern.
Now for referencing which one that turns out to be, if you have excel 365 then I recommend you use LET() and then you don't have to re-use the same calculations multiple times in the same formula. so something like:
=LET(PTS, D2:D9,
GD, C2:C9,
rPTS, 10*PTS+GD,
rList, SORTBY(B2:B9,pPTS,-1),
TAKE(rList, 1) )
Now for referencing which one that turns out to be, if you have excel 365 then I recommend you use LET() and then you don't have to re-use the same calculations multiple times in the same formula. so something like:
=LET(PTS, D2:D9,
GD, C2:C9,
rPTS, 10*PTS+GD,
rList, SORTBY(B2:B9,pPTS,-1),
TAKE(rList, 1) )