# top N values

#### brycos

##### New Member
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

### 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.

##### MrExcel MVP
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.

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)),"")

Replies
2
Views
139
Replies
8
Views
97
Replies
3
Views
84
Replies
12
Views
177
Replies
3
Views
64