Using LARGE function to get top rankings, but problem with duplicates

[heweaver]

Attached is a sample of my data. I have a list of about 30 names with a ranking number of sorts (we input data to determine rankings based on attendance).

I'm using a combination of the LARGE function as well as the MATCH / INDEX to report the name (which is to the LEFT of the ranking number). The LARGE function is working as expected, except when there are duplicates it will report only the first name twice. In this sample, you can see two people happen to have a rank of 33 but only the first name is reported.

Is there any formula or array I can use to get around this? Some other way to get the correct names with the rankings? Thanks in advance!

[BadlySpelledBuoy]

One quick and easy way would be to use a helper column to unique rank the rankings you manually enter. Depending on your criteria for doing that you may be able to calculate that as a unique rank too.

In the attached I've used the below formula in M3 and copied down.
=RANK(C3,$C$3:$C$30)+COUNTIF(C$3:C3,C3)-1

I've then uses a simple INDEX/MATCH with ROWS to increment the numbers rather than having to amend each formula to suit. So this in K3 and copied down:
=INDEX(B:B,MATCH(ROWS(A$1:A1),M:M,0))

The addition of the COUNTIF section of the first formula will ensure a unique rank and that means no duplicates in the lookup table.

BSB

[heweaver]

This did exactly what I needed it to! (I had to tweak it a bit as my actual data had slightly different columns, but this is EXACTLY what I wanted!) Thank you so very much!

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[BadlySpelledBuoy]

Glad I could help

BSB

[jason.b75]

Without the extra helper column that BSB has added,
E3 and fill down (almost the same as your existing formula, but you don't have to change each one manually, as BSB pointed out).

Please Login or Register  to view this content.

F3 and fill down

Please Login or Register  to view this content.