Excel Forumla (vlookup problem)

[Guest]

Hello,
I need a little help with a problem using excel Rank and Vlookup.
My spread sheet has 4 columns and 288 rows.

( Players A2 ) (Account #B2) ( Credits C2 ) ( Ranking D2)
John Smith 2020 100 1
Pual Johnson 2021 90 2
Steve Steel 1011 100 1

I rank the Players using "=Rank(c2,c2:c4)" in column D and Vlookup to pull
the data from the table above into a top 29 winner list. However, when there
is a tie (D2 and D4), Vlookup never finds the A4 player but returns A2
Player. I am trying to find a set of forumla that will automatically sort
for me. That will keep me from hand sorting a "top 29 Winners" list.

Any Ideals?

 

[Guest]

You could add a countif function to avoid posting any ties... If your real
rank function is =rank(c2,c$2:c$289), augment that function beginning in row
3: =rank(c3,c$2:c$289)+countif(c$2:c2,c3). Any ties are effectively broken
by the countif, in favor of the entry appearing earlier in the list.