• 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

    Hui...

  • 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

Ryan91486

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!
 

Attachments

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!
 

Ryan91486

New Member
If there are no ties then you can use below formula in cell B15
=INDEX($A$2:$A$12,MATCH(LARGE($B$2:$B$12,ROWS($B$12:B12)),$B$2:$B$12,0))
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.
 

Attachments

shrivallabha

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
https://chandoo.org/forum/threads/tiebreaker-formula-not-working-as-expected.36011/

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