Identifying top scores

[Guest]

Hi

I have an array of scores running down one column which correlate to staff
names in another column. I am looking to identify (by name) the top 2
scorers. This in itself seems easy enough except when the top 2 scores are
the same. I am using the Large formula to determine the top scores and can
then use a vlookup to retrieve the staff names however when the scores are
identical I keep ending up with the same name twice. I assume I need to
somehow reference the exact cells of the scores to give me different names
but nothing I have tried (Match/Index) has worked. Any help would be
fantastic

Cheers
Soph

 

[Guest]

You need tiebreakers.

Here's one way using non-array formulas to drive it out ..

Illustrated in this sample:
http://www.flypicture.com/download/ODg1Mw==
Auto sort descending w tiebreakers.xls

Source data assumed in cols A and B, from row2 down.
Names in col A, scores in col B

In D2:
=IF(B2="","",B2-ROW()/10^10)
Leave D1 blank

In E2:
=IF(ROWS($1:1)>COUNT($D:$D),"",INDEX(A:A,MATCH(LARGE($D:$D,ROWS($1:1)),$D:$D,0)))

Select D2:F2, copy down to cover the max expected extent of data in col B.
Hide away col D. Cols E & F will return the required auto-sort of the names &
scores in descending order by scores. Names with tied scores, if any, will be
returned in the same relative order that they are listed in the source cols.

 

[Max]

Just read-off the top xx scorers in cols E and F, as desired.

---