# 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

• 862 KB Views: 15

#### shrivallabha

##### Excel Ninja
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...

#### 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
The term
ROWS(\$B\$12:B12)),
is intended to generate a sequence of values when copied down, giving second and third places.

#### Ryan91486

##### New Member
The term
ROWS(\$B\$12:B12)),
is intended to generate a sequence of values when copied down, giving second and third places.
Got it. Thanks a bunch!

#### Ryan91486

##### New Member
Is there a way to get around ties?

#### Ryan91486

##### New Member
Maybe showing two first places then a third if 1st is in a tie?

#### 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

• 11.4 KB Views: 4

#### 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