top N values

brycos

New Member
Joined
Dec 19, 2005
Messages
10
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
 

Some videos you may like

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
brycos said:
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
ABCDEF
14
21
3NameScoreRankTop 4
4dawn234brian
5damon235greg
6jon253jon
7nate226dawn
8christine217damon
9greg322 
10brian351 
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)),"")
 

Watch MrExcel Video

Forum statistics

Threads
1,118,167
Messages
5,570,639
Members
412,334
Latest member
ExcelForLifeDontHate
Top