top N values

[brycos]

i have a relatively short list. i would like to easily capture the top N values within the list. i have setup two columns
columnJ columnK
=LARGE(I$29:I$63,1) =VLOOKUP($J65,$I$29:$J$63,2,0)
=LARGE(I$29:I$63,2) =VLOOKUP($J66,$I$29:$J$63,2,0)
... etc

problem is when i have two or more values in columnJ i get the same result in columnK. is there an easier way to avoid the following not nearly perfect code
=IF(VLOOKUP(J66,I$29:J$63,2,0)=K65,IF(VLOOKUP(J66,I$33:J$63,2,0)=K65,IF(VLOOKUP(J66,I$36:J$63,2,0)=K65,IF(VLOOKUP(J66,I$41:J$63,2,0)=K65,IF(VLOOKUP(J66,I$45:J$63,2,0)=K65,IF(VLOOKUP(J66,I$51:J$63,2,0)=K65,IF(VLOOKUP(J66,I$53:J$63,2,0)=K65,VLOOKUP(J66,I$56:J$63,2,0),VLOOKUP(J66,I$53:J$63,2,0)),VLOOKUP(J66,I$51:J$63,2,0)),VLOOKUP(J66,I$45:J$63,2,0)),VLOOKUP(J66,I$41:J$63,2,0)),VLOOKUP(J66,I$36:J$63,2,0)),VLOOKUP(J66,I$33:J$63,2,0)),VLOOKUP(J66,I$29:J$63,2,0))

bc

 

[Aladin Akyurek]

i have a relatively short list. i would like to easily capture the top N values within the list. i have setup two columns
columnJ columnK
=LARGE(I$29:I$63,1) =VLOOKUP($J65,$I$29:$J$63,2,0)
=LARGE(I$29:I$63,2) =VLOOKUP($J66,$I$29:$J$63,2,0)
... etc

problem is when i have two or more values in columnJ i get the same result in columnK.

...

The lookup functions are designed to return the first match, so you cannot retrieve multiple records having the same key value.

Adjust the ranges to suit your sheet's layout.

Construct Top N List.xls
	A	B	C	D	E	F
1				4
2				1
3	Name	Score	Rank	Top 4
4	dawn	23	4	brian
5	damon	23	5	greg
6	jon	25	3	jon
7	nate	22	6	dawn
8	christine	21	7	damon
9	greg	32	2
10	brian	35	1
11
12
13
Data

! OTHEWISE, ERROR OF JavaScript OCCUR.</FONT></CENTER>

Formulas...

C4, copied down:

=RANK(B4,$B$4:$B$10)+COUNTIF($B$4:B4,B4)-1

D1: 4

which is N of Top N.

D2:

=MAX(IF(INDEX(B4:B10,MATCH(D1,C4:C10,0))=B4:B10,C4:C10))-D1

which needs to be confirmed with control+shift+enter (not just with enter).

D4, copied down:

=IF(ROW()-ROW($D$4)+1<=$D$1+$D$2,INDEX($A$4:$A$10,MATCH(ROW()-ROW($D$4)+1,$C$4:$C$10,0)),"")

or

=IF(ROWS($D$4:D4)<=$D$1+$D$2,INDEX($A$4:$A$10,MATCH(ROWS($D$4:D4),$C$4:$C$10,0)),"")