EXCEL FORMULA

CURTIS775 see attached

In sheet 4 I used the "old" style of formulas were you create a  formula and copy or drag down (and/or across) to fill in the rest of the formulas.  In sheet 3 I inserted some array formulas where you only enter the array in 1 cell and the rest of the array 'spills' over to the other corresponding cells.

That all said, I also moved the players in the upper score table be in the same order as the player list in the lower table to avoid needing a lookup equation.  Also, I notice a couple of "error" in the scoring example and a couple cases of that 2 under par (eagle) that was scored as a '5' but the equation I proposed scores it as a '7' (see orange cells on sheet 'event 2').  If you prefer it max out at 5 points then the equation could be tweaked to be:

=MIN(MAX(3 - 2 * (C2 - C$1), 0),5)

but my clever 2-d array equation on sheet 3 would be a bit more difficult and would be easier to just use an array equation for each hole.

I also updated the equation for YTD Total to use "3-d array" to make it simpler:

=SUM('event 4:event 1'!P17)

You can get fancy and make that a general equation to automatically update as you add additional sheets but I won't go there.

Hope this helps.