[SOLVED] Number Ranking Issue

[TMelanson]

Hey Gang,
I am using Excel 2003 and I created a sports pool spreadsheet. I am trying to associate the top two points in a range of 20 numbers with a corresponding range of 20 names.
Example

Cell A10:A29 is where the names are listed.
Cells AT10:AT29 have the total points

Example:
A AT
10 Todd 50
11 Sherry 80
12 Ashley 92
13 Trent 9
14 Kayla 4


This is what Im looking for:
1st formula answer = Ashley
2nd formula answer = Sherry

Ive been messing with the match function with no luck.
Any help would be appreciated.

Hoping someone can help a newbie out!

[royUK]

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

[NBVC]

Assuming you might have people with the same highest points..then it is not just a simple single formula solution....

In a new column, say AU10, enter:

=RANK($AT10,$AT$10:$AT$29)+COUNTIF($AT$10:$AT10,$AT10)-1

and copy down.

This lists the rankings in order without duplicating rank number


then use this formula:

=INDEX($A$10:$A$29,MATCH(ROWS($A$1:$A1),$AU$10:$AU$29,0)) to get first top rank, and copy down to next row to get next top rank, whether it is of the same score or next highest.

[TMelanson]

NBVC,
It worked perfectly!
Thank you so much for your quick resolution!

[NBVC]

You are welcome and thanks for taking the time to read and comply with the rules.

[TMelanson]

NBVC,
The calculation worked initially, but if I change some of the data that changes the points, I am getting #REF! as a result

[TMelanson]

Let me be more specific, the RANK formula is working fine. Its the INDEX formula throwing the #REF!

[NBVC]

What data did you change exactly...

What is the formula you are using?

Perhaps attach sample workbook.

[TMelanson]

My internet connection here at work is pretty bad... The window for adding attachments will not open for me. Just times out. Ill try it again when I get home.
Thanks

[NBVC]

In this formula:

=INDEX($A$10:$A$29,MATCH(ROWS($A$1:$A1),$AU$10:$AU$29,0))

the A10:A29 is the range containing the names...

The AU10:AU29 is the corresponding Rank Formula range...

The Rows($A$1:$A1) returns 1 and as you drag down it returns 2, 3, etcc...

Make sure the first formula has that Rows() function as written above...

Reference error means you are referencing something incorrectly or it doesn't exist...

Just check the formula again..

[TMelanson]

"The Rows($A$1:$A1) returns 1 and as you drag down it returns 2, 3, etcc...

Do both of the 1's here change per cell?
Ive attached the spreadsheet.

[NBVC]

only the 2nd "1" changes. Note it doesn't have the $ in front, which is used to "freeze" a row or column, so when you drag down the formula you reference the same location all the time.. without the $ you reference the relative location...

BTW... no attachment....

[NBVC]

Okay.. saw that you put the attachment in your original post...

Maybe my fault, but the ranges in the Index and Match functions weren't of the same size:

Try:

=INDEX($A$10:$A$29,MATCH(ROWS($A$1:$A1),$AU$10:$AU$29,0))

and to not sure 0's when no name in column A... change to:

=IF(A10="","",INDEX($A$10:$A$29,MATCH(ROWS($A$1:$A1),$AU$10:$AU$29,0)))

and copy down.

[TMelanson]

Thats great, Thank You! I'm going to be hiding those columns anyway.
One last question, with the possibility of more than one person getting the same score, is there a check that can be done to see if more than one person has a same high score?

[NBVC]

Something like this in the next column:

=COUNTIF($AT$10:$AT$29,AT10)

copied down will tell you how many repeats of the same score as the one in that same row there are. Is that what you meant?

[TMelanson]

Yes but just for the high score duplicates.
Would it be easier to somehow add the points to the end of the persons name?

[TMelanson]

Sorry, maybe this may be easier..
Cell B34 and B35 have the winner and runner up.
Is there a way to add the score to the end of the name at this location?
This way I can visually see the tie and adjust accordingly

[NBVC]

You mean in B34?:

=AV10&" "&VLOOKUP(AV10,$A$10:$AT$29,46,0)

copied down...

You can also separate that Vlookup into next cell if you want separate cells for name and score.

[TMelanson]

YES!!!!
Thats it!

Thank you so much for your help and time.
I truly appreciate it!