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)

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.

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.

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.

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.

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.

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

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.

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.

Please start your own thread. Adding your problem on to a thread that is 3 years old is hijacking.

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.