Problem using SUMPRODUCT and MATCH to rank golf teams scores if the scores are drawn

Hi,

I'm trying to rank scores from a golf team tournament using SUMPRODUCT and MATCH. I've searched through past posts but unable to find a working solution.

The problem I'm having is when two team scores or more are drawn after 18 holes. The SUMPRODUCT function I've used to rank the teams shows the ranking order correctly (which didn't work using the RANK function) when there is a drawn score, but there is a #N/A error when then using the MATCH function to match the team against it's rank. I understand why this is happening because one of the team matches is not available from the ranking when there is a draw and unfortunately I can't find a way of solving this issue.

Further, if there is a draw after 18 holes then there is what is called, a 'countback' on the back nine holes and then the back 6 holes if still need to split the teams. I'm having a problem filtering these draws through, from a draw after 18, through to which drawn team then won the back 9 etc. and then finally ranking all teams correctly at the end. I've used increments before to separate drawn teams but in this case it doesn't really work that well as there have been occasions when testing the functionality where the team who actually won after 18 is not longer in 1st position after the countback.

I hope this all makes sense and I have attached a copy of the spreadsheet to help with the understanding of the problem.

TIA,

Chris

Ooops. Sorry about that.

With a helper column in W to sum the back 6 you can use this to get the ranking score

Formula:

=SUM(CHOOSECOLS(V2:X2,-1,1,2)*10^SEQUENCE(,3,0,-3))

and to get the teams in order you can use

Formula:

=SORTBY(B2:B7,Z2:Z7)

It took me a bit to work out what's happening with the formula but that seems to be a great way of tackling the problem and a way I wouldn't have thought of; so thanks for that.

Thanks very much.

Glad to help & thanks for the feedback.