Retrieve highest 3 scpring players

**BennyHill** · 04-24-2013, 10:20 AM

Hi All

Im trying to devise a formula to select the highest 3 scoring players from a table, I've been using

=INDEX(A19:A24,MATCH(LARGE(G19:G24,1),G19:G24,1))
=INDEX(A19:A24,MATCH(LARGE(G19:G24,2),G19:G24,2))
=INDEX(A19:A24,MATCH(LARGE(G19:G24,3),G19:G24,3))

But it is behaving strange and not retrieving the correct data.

Ideally once identified who comes 1st, 2nd, 3rd in each catergory I can allocate 3 points to 1st, 2 points to 2nd and 1 points to 3rd. Then add together all the points total and display a league table for the overall points total alongside the relevant players name. I've attached what I've got so far.

**Jacc** · 04-28-2013, 04:03 AM

Your formulas in B27:B29 are a bit off.
By using $ in the formulas (hit F4 when editing a formula) some ranges can be held fixed while others move as you copy the formula down. This is a real timesaver and avoids errors.

On a sidenote I recommend you to avoid using merged cells, they usually just cause trouble.

**AZ-XL** · 04-28-2013, 04:15 AM

b 27 cell =
=INDEX($A$19:$A$24,MATCH(LARGE($E$19:$E$24,ROW(A1)),$E$19:$E$24,0)) Ctrl+shift+enter
Unmerge the cells befor entering formula

**popipipo** · 04-28-2013, 05:49 AM

Given formulas does not work correctly with equal points
Try therefore this one

**Tony Valko** · 04-28-2013, 10:07 AM

This will work on your sample data...

Entered in B27 and copied down to B29:

=INDEX(A$19:A$24,MATCH(LARGE(E$19:E$24,ROWS(B$27:B27)),E$19:E$24,0))

However, is it possible to have ties in the total value? If so, then there may be more than 3 items in your top 3 list depending on where the ties are.