Here is a sample of my sheet that I am having issues with. I need to get the names of the Top 5 scores. In the Top 5 column, I have used the =LARGE() function to grab the 1-5th place scores. I would like to also pair that with the Name next to the score. I know a simple sort would do the trick, however, I would prefer a formula to automate the info for me.
Everytime I create the vlookup formula, I get a formula error. My formula is as follows:
The error returned is that it is finding "Anthony Cavanaugh" in value 3 where a number is expected.
If you already have a scheme for tie breaking, you're set to go. My favorite tie break scheme is to handle it before ranking, not after. That way you will never have to deal with a tie and you can use either LARGE or RANK without any problem.
Of course you'll want to use LOOKUP no matter what other choices you make because, as you say, it prevents having to sort.
Here's the tie-break scheme. Your total points score is in the 1-100 range and your weekly average is in the 0 to 1000 range. Create a tie-broken TotalPoints aux. column that consists of TotalPoints + WeeklyAverage/10,000. Now two otherwise equal TotalPoints scores will be offset by the difference in their WeeklyAverage scores, divided by 10,000. Unless their weekly averages are identical, there will be no tie. If there is still a possibility of a tie, take a third factor and add it in an even smaller fraction.