Extracting the Top n Maximum Values in Excel

Suppose you have a file with a list of student names and the number of votes they received in the student council election.  Now assume that, for whatever reason, you can’t or don’t want to sort that list to determine the top 5 vote getters.  So you are looking for create a formula that returns a column of student names with the top 5 votes.

Assume your worksheet has the number of votes in Column A starting in Cell A2 through A100, the corresponding student name in Column B starting in Cell B2 and running through B100, and you would like the list of top 5 votes in Column D starting in Cell D2.

In this case, the formula would be:

=INDEX($B$2:$B$100,MATCH(1,INDEX(($A$2:$A$100=LARGE($A$2:$A$100,ROWS(D$1:D1)))*(COUNTIF(D$1:D1,$B$2:$B$100)=0),),0))

copied down in this case 5 times through cell D6.  This formula will work even if there are tied votes between different students.