Index/Rank Problem, based on criteria, if match then tiebreaker

[brotherwo]

THe first part I have been able to complete (rank players from lowest to highest), but the problem Im running into is if there is a tie I need it to be broken by my tiebreaker table. I've attached a clear example.

Thanks for the advice!


Objective:

Create a leaderboard that ranks players from lowest score to highest and if there is a tie score between two teams have the place decided by the tiebreaker table (lowest score on 1st Hardest Hole, if tie, lowest score on second hardest hole, etc..)

For example, in my leaderboard Colin and Jack rank 3rd and 4th since they tied with Jay & Homer. What I want to occur is to recognize the tie and then compare the teams score on the hardest hole (column AB). If hardest hole is a tie, move on to the second hardest hole.


Result: Jay and Homer should take 3rd place since they had the lower score on the 4th hardest hole (both teams had the same scores on the 1st, 2nd and 3rd hardest holes)
Jay & Homer 64 - 3, 3, 4, 3
Colin & Jack 64 - 3, 3, 4, 4

another example is ...
Ryan & Chuck 68 - 3, 3, 4, 4, 3 (5th place)
Pat & Scott 68 - 3, 3, 4, 4, 4 (6th place)

[brotherwo]

bump, any ideas?

[brotherwo]

someone has to have some insight on this one, thanks in advance!

[JBeaucaire]

1) Put this in AJ5 and copy down:

=(Y5&"."&AB5&AC5&AD5&AE5&AF5&AG5)+0


2) Put this in AK5 and copy down to get the rank for each row:

=RANK(AJ5, $AJ$5:$AJ$11,1)


3) B17, copied down:

=INDEX(A$5:A$11, MATCH(A17, $AK$5:$AK$11, 0))


4) C17, copied down:

=INDEX(B$5:B$11,MATCH(B17, $A$5:$A$11, 0))

[brotherwo]

Awesome! Thanks so much