Find positions by value and return adjacent cell

[Ryan91486]

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!

 

[shrivallabha]

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]

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]

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]

The term
ROWS($B$12:B12)),
is intended to generate a sequence of values when copied down, giving second and third places.

 

[Ryan91486]

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]

Is there a way to get around ties?

 

[Ryan91486]

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

 

[Peter Bartholomew]

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.

 

[shrivallabha]

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

Formula or VBA for upgrading the grade one/two or Nil subject among five subject with various condition.

I want Fix a formula in D6:D10 for upgrade the grade ( Based on C6:C10) of only one /two subjects or Nil subject acording to no.mentioned in C2 cell. Upgradation will be from lower grade to upper grade. in C=0 then D6:D10 WILL BE SAME AS C6:C10, IF C=1, THEN ONLY ONE SUBJECT WILL BE...

www.mrexcel.com