Vlookup function returns duplicate values

[Bigbelt]

It's been a long time since I wrote any formulas, and it is showing.

I have a stat sheet for my basketball players, I am trying to pick the top 3
in a certain category. Everything goes great, until there are duplicate
values that are associated with different player names.

I need to have it not only sort the top three values, but return the exact
three names that go with each value (which in some cases is the same value).

Last FT %
Harriss 66.67% Harriss
Wiger 50.00% Wiger
Shuck 50.00% Shuck
Ostergard 66.67% Ostergard
McCann 0.00% McCann
Usselman 66.67% Usselman
Bryant 0.00% Bryant
Chamberlain 42.31% Chamberlain
Fletcher 50.00% Fletcher
Liebelt 60.00% Liebelt
Hamilton 25.00% Hamilton
Maney 33.33% Maney
Voorhees 37.50% Voorhees
Player 0.00% Player


Player FT %
Harriss 66.67%
Harriss 66.67%
Harriss 66.67%

Formula for finding the FT% is:
=LARGE(B2:B15,1)
=LARGE(B2:B16,2)
=LARGE(B2:B17,3)

Formula for finding the Name associated with the FT% value is:

=VLOOKUP(B19,B1:C15,2,0)
=VLOOKUP(B20,B1:C16,2,0)
=VLOOKUP(B21,B1:C17,2,0)

Now I understand why it is returning the same value, because of the simple
logic involved, however I am looking for a condition to write to add to this
so that it picks the next name involved and appropriately returns it.

I hope this made sense.

 

[Bigbelt]

Is there a "sort function" that can be nested into the vlookup formula?

 

[RaulDR]

Is there a "sort function" that can be nested into the vlookup formula?

hi ron,

instead of using "large" i think you should insert a column to rank your data
rank(b2, $b$2:$b17) then use the resulting rank value to vlookup the name
and the percentage.

hope this help.

 

[Bigbelt]

Appreciate the idea, tried it - but it still returns the duplicates as being
first (#1). Might work, but needs some more arguments to still rank them
with no duplicates.

 

[RaulDR]

hi ron,

consider the following example

Stat Rank with tie helper column to break the tie Final rank
66.7 1 2
2
23.5 4 1
4
33.7 3 1
3
66.7 1 1
1

using the formula

rank with tie

=RANK(A2,$A$2:$A$5)
=RANK(A3,$A$2:$A$5)
=RANK(A4,$A$2:$A$5)
=RANK(A5,$A$2:$A$5)

helper column

=COUNTIF(B2:$B$5,$B2)
=COUNTIF(B3:$B$5,$B3)
=COUNTIF(B4:$B$5,$B4)
=COUNTIF(B5:$B$5,$B5)


final rank

=IF(C2>1,B2+1,B2)
=IF(C3>1,B3+1,B3)
=IF(C4>1,B4+1,B4)
=IF(C5>1,B5+1,B5)


hope this helps.

 

[Bigbelt]

Well this worked for two duplicate numbers, but did not work when there were
three that were duplicated. I just tried it and it returned two ranked as 2.
If you add one more 66.7 to your list you will also see it does the same
thing. So it worked for duplicates, but not triplicates and beyond.

Any other ideas? Appreciate you giving me possibilities.