I'm doing like a bit of a competition for my students in which they have a weekly test they have to complete and submit. The grade is stored in an excel column next to their names.
Following instructions i found, i was able to create a full working general TOP3 with the Average of the tests' grade and when i get to the TOP5 for the grades of the last submitted test, i get a three-way tie.
I use the LARGE function to find the top grades and the combination of the INDEX and MATCH functions to find and display the name associated to that mark. (Something like this =INDEX($A$1:$A$29;MATCH(M12;$F$1:$F$29;0))
The problem is that the function compares the grade on it's left to find that value in the range of grades and then returns the corresponding name associated to that row; so, it returns the same name for the three grades.
I tried using an IF function to exclude the first-result-cell from the array in which the formula is looking so that when it finds a match it will be different from the previous one, but i have not manage to work it out...
Here is a link that @Jeeped gave which basically solves the problem! Thank you all for commenting!
Multiple Ranked Returns from INDEX(…) with Duplicate Values: http://tinyurl.com/naavhgf