Oops, I didn't notice that your list had duplicate entries. That's not going to work with a simple INDEX(MATCH) setup as it will always pull the first match. Also, I think you had a small error in your typed formula; after MATCH on the first row, I think you meant F5, not F6.
Also I just realized that your "problem" statement doesn't require that it be done in a single formula. The most basic way of achieving it would be to copy the applicant and test mark cells to another spot, sort on the test mark column, and delete the ones below the top 10. Another way would be to use a pivot table, and then sort on the Test Mark values column, and filter to the top 10. That might be the easiest way to do it, plus you could make your original data into a table so that when you add rows below they will be included and you only have to refresh. As you can see in the example, I added a rank column (with lots of ties) and the # of times the test is taken (the test mark is an average). Just some ideas.
|