@Garry's Student for some reason I didn't find the formulae I needed earlier. When I couldn't get back to this page for a couple of days a little more research found me the RANK formula. (In the newest Excel, RANK is depreciated so I used the newer RANK.EQ instead. And then I thought of VLOOKUP but with a bit more searching I found INDEX MATCH which seems better, and you used that too.
So here's what I came up with and how I coped with the duplicate years:
spreadsheet ranking names by year
(My formulae start at row #2 due to the headers)
Column C formula:
It's important to know how the ranking works. If there are three items in position number 5, they are all given a rank of 5, and there will be no items with a rank of 6 or 7. The next position is a rank of 8.
Column D formula:
in the first cell
and in subsequent cells
This gets rid of the duplicate ranking. The ranking is increased by 1 if there is a duplicate rank above in the list. So those duplicate number 5s will be given the rank of 6 & 7.
Column E is simply a list of consecutive numbers, pretty easy to autofill.
Column F formula brings it all together:
Anyway I wanted to share what I had as it works and is a little different. But probably the answer from Garry's Student is better as it uses fewer columns to get there. (Possibly I could combine some and use fewer columns too with my method.)