Like DerbyDad03, I do not understand why you need a formula to cretae text "First", "Second" etc.
This text is only going to occur once - in a column to the left of names and scores.
For this example each class has results in the same columns but on different worksheets. You could have all results on the same worksheet but in different columns.
The results look like this:
A B C D E
1 Class #1 Class #2
2 Rank Name Score Name Score
3 First Name Q 98 Name X 95
4 Second Name D 98 Name V 94
5 Third Name N 94 Name E 93
6 Fourth Name E 89 Name L 92
7 Fifth Name Z 88 Name M 91
8 Sixth Name U 88 Name Z 86
9 Seventh Name T 83 Name G 86
10 Eighth Name F 82 Name C 86
11 Ninth Name R 81 Name A 84
12 Tenth Name V 78 Name N 80
The names and scores are all filled using VLOOKUP()
In cell B3 use this formula:
In Cell C3 use this formula:
The data on sheet2 is organized like this:
A B C D E
1 Class #1
2 Name Score Extended score Rank Name
3 Name A 78 78.00003 11 Name A
4 Name B 44 44.00004 18 Name B
5 Name C 63 63.00005 12 Name C
6 Name D 98 98.00006 2 Name D
The reason for the extended score is to stop RANK() returning two ranks the same.
If two individuals have the same rank, the VLOOKUP() function will not return the name of the second individual.
The extended rank is created by adding a very small value to each score to make it unique.
In C3 enter this:
This is the row number divided by 100,000, never enough to reach 1 and alter a score's position in the table, only enough to make two equal scores different and be ranked consecutively.
You may need to use a smaller value, e.g., divide by 1,000,000 if scores have one decimal place.
The reason for the second column of names is to enable the name to be returned by VLOOKUP using the rank.
The real score is returned from column B, using VLOOKUP and the individual's name.
The rank formula in Cell D3 is:
The formula in E3 is just:
All formulas are just dragged down, with the exception of the first result column where cell B3 contains the rank number to lookup - 1 in B3
=VLOOKUP(1,Sheet2!$D$3:$E$28,2,FALSE), which has to be changed to 2, 3 , 4 etc. to 10.
You could use the approach DerbyDad03 suggested using the Row number to create the number.
The results data in columns B and C can be copied and pasted to columns D and E and then use Find-replace to change the worksheet name.