Complicated lookup involving multiple Max values and secondary conditions - WORLD CUP EDITION

Copper Contributor

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:



For cells D2 through D8, I want to get the "winner" of that group of 4. This is a complicated prospect. If the max value is unique, it's the max value. However, if there are more than one of the max value (in this case, "4"), then I need the formula to reference C2 through C8 only for those duplicate max values (in this case, C2 and C8) and for whichever of those numbers is largest, return the original max value from column D. In the case that those two numbers also match, I need to reference a third column the same way (find the max of two adjacent values). 

Does this make sense?

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

This sounds like it can be done. It's a task that could get convoluted very quickly though. It would help to see all the checks listed beyond column C then a formula could be drawn up. MODE might be useful to determine if there are duplicate max entries.  If MODE produces an error, the max is unique, etc.