# rank lookups

1. ## rank lookups

Hello World!! It's me, Alex I have attached a spreadsheet. I have a list of names and a list of numbers. And then I rank the numbers, returning the values of the 4 largest numbers in the list. Now, I have a column called Name, and I would like to return the names that correspond to the values. I assume I would use an index match or something like that. Let me know Danks.

2. ## Re: rank lookups

You dont show a fomula for the ranking, so use this, copied down...
=LARGE(\$B\$2:\$B\$6,ROW(A1))
And then for the names...
=INDEX(\$A\$2:\$A\$6,MATCH(F2,\$B\$2:\$B\$6,0),1)

3. ## Re: rank lookups

Here is a variation for getting the largest numbers;

Select 4 cells F2:F5 where you want the largest numbers to be entered then enter this formula in the formula bar:

Formula:
`Please Login or Register  to view this content.`
Enter with Ctrl+Shift+Enter (array formula)

This will list in descending order the 4 largest number in column B.

Now use the same formula supplied by FDibbins for column G and copy down.

4. ## Re: rank lookups

Here's another method that accounts for ties.

A top/bottom list may have more than N items that fall within N if there can be ties (duplicate number values).

In the following example I have the number values sorted in descending order just to make it easier to see what items are in the top 4. In this example there are actually 6 items that fall within the top 4.

Data Range
 A B C D E F G H 1 Name Score Top N 4 Name Score 2 Sue 94 Count 6 Sue 94 3 Tom 84 Tom 84 4 Bob 84 Bob 84 5 Jim 83 Jim 83 6 Lisa 83 Lisa 83 7 Becky 83 Becky 83 8 Jim 80 9 Gina 77 10 Tim 75

Enter the number of the top items you want in cell E1. If you want a top 5 list then enter 5. If you want a top 3 list then enter 3. In this example we're doing a top 4 list.

Enter this formula in E2. This will return the count of records that fall within the top N.

=COUNTIF(B2:B10,">="&LARGE(B2:B10,E1))

Enter this formula in H2:

=IF(ROWS(H\$2:H2)>E\$2,"",LARGE(B\$2:B\$10,ROWS(H\$2:H2)))

Enter this array formula** in G2:

=IF(ROWS(G\$2:G2)>E\$2,"",INDEX(A:A,SMALL(IF(B\$2:B\$10=H2,ROW(B\$2:B\$10)),COUNTIF(H\$2:H2,H2))))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Select G2:H2 and copy down until you get blanks.

5. ## Re: rank lookups

If you want/need to break ties you could use this in column C2 and copy down

Formula:
`Please Login or Register  to view this content.`

6. ## Re: rank lookups

If you want to include a rank column...

Data Range
 A B C D E F G H I 1 Name Score Rank ---- Top N 4 ---- Name Score 2 Sue 94 1 Count 6 Sue 94 3 Tom 84 2 Tom 84 4 Bob 84 3 Bob 84 5 Jim 83 4 Jim 83 6 Lisa 83 5 Lisa 83 7 Becky 83 6 Becky 83 8 Jim 80 7 9 Gina 77 8 10 Tim 75 9

Enter this formula in C2 and copy down. This will return the ranks with a tie breaker.

=RANK(B2,B\$2:B\$10)+COUNTIF(B\$2:B2,B2)-1

Enter this formula in F2. This will return the count of records that fall within the top N.

=COUNTIF(B2:B10,">="&LARGE(B2:B10,E1))

Enter one of these formulas in H2 depending on what version of Excel you're using.

For ANY version of Excel:

=IF(ROWS(H\$2:H2)>F\$2,"",INDEX(A\$2:A\$10,MATCH(ROWS(H\$2:H2),C\$2:C\$10,0)))

For Excel versions 2007 and later:

=IFERROR(INDEX(A\$2:A\$10,MATCH(ROWS(H\$2:H2),C\$2:C\$10,0)),"")

Enter this formula in I2:

=IF(H2="","",VLOOKUP(H2,A\$2:B\$10,2,0))

Select H2:I2 and copy down until you get blanks.

7. ## Re: rank lookups

Here is your original workbook with formulae to rank the values and to break ties if they exist then list the 4 largest values with the name associated with them.

8. ## Re: rank lookups

If you break ties and only include the 4 largest (based on the sample data in my post) then some people are getting screwed!

9. ## Re: rank lookups

There were no parameters given for how open-ended the problem is. No matter what the formula is, someone can get "screwed". As long as the tie breaking rules are known and understood beforehand there shouldn't be problems. I have seen a coin-toss as the tie breaker in a contest after all the rules for tie-breaking had been exhausted.

10. ## Re: rank lookups

That may be true but statistically speaking, the technique I demonstrated is the correct method for generating a top/bottom list unless special considerations are to be made.

11. ## Re: rank lookups

I used the formula that entered in H2, enable me to extract the 5 top percentages, however, it doesn't enable me to match the names with the percentages.

12. ## Re: rank lookups

Just start a new thread and include a workbook that demonstrates your problem.
Directions:
Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1