• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Find positions by value and return adjacent cell


New Member
Below is a picture of a spreadsheet i created for calculating point per player. I would like 1st-3rd position from B2-B12 to be calculated and return the corosponding name for that player (A2-A12). Like B15=1st players name, B16=second players name, B17=third players name.
Not too worried about ties but this may come into play later.
Any help would be greatly appreciated!


Peter Bartholomew

Well-Known Member
The 'modern' Excel solution might be
= INDEX( SORTBY(player, points,-1), {1;2;3} )
in which SORTBY returns the entire player list and INDEX selects the first three names.

Note: at present 'future' Excel solution might be a more accurate description!


New Member
If there are no ties then you can use below formula in cell B15
copy down...
This works great for first. For second place I’m able to change the 0 to 1. How do i get third place?

Peter Bartholomew

Well-Known Member
Yes, it is quite possible to do by making a small adjustment to the scores, sufficient to act as a tie-breaker but not so large that it alters the overall ranking. I have attached a spreadsheet based upon @shrivallabha's formula but there is a catch in that to work on a spreadsheet I change it by introducing names and array formulas. The original formula becomes
= INDEX( Player, MATCH( LARGE( Score, k ),Score, 0) )
where k={1;2;3}

To generate the adjusted score I subtracted the row number
= Score - ROW(Score)/1024
so that the final formula becomes
= INDEX( Table, MATCH( LARGE( adjScore, k ), adjScore, 0), {1,2} )

A bit of work would revert the workbook to a traditional programming style.



Excel Ninja
Is there a way to get around ties?
What will be life if there were no "ties" ;)

I was expecting it. You need to provide a tie-breaker logic in order to get desired results. In the past, there was one discussion; check if you find it useful

This is one more example of approach which I had worked out for on MrExcel