Results 1 to 3 of 3
Thread: Vlookup (2000)

20040205, 02:29 #1
 Join Date
 Dec 2002
 Posts
 192
 Thanks
 0
 Thanked 0 Times in 0 Posts
Vlookup (2000)
Hi, I have a vlookup problem. I can only get one name to show up and there should be two different names showing up. Please look at the attachment.
Thanks for any help

20040205, 04:42 #2
 Join Date
 Dec 2000
 Location
 Salt Lake City, Utah, USA
 Posts
 9,508
 Thanks
 0
 Thanked 6 Times in 6 Posts
Re: Vlookup (2000)
Vlookup will only return a single value. Test this formula to see if I got it right for the 2nd and subsequent. My source is the always excellent Chip Pearson webite, lookups page.
Cells I12:J15, must be entered as array formulas, using CtrlShiftEnter. You only need to enter the two in cells I12:J12 and copy them down. The ranking 1 through 5 will show the first 5 matches to the key number. If there are less than 5, the invalid ones will return an error. The formula can be copied down further for as many matches as you wish, but the numbering has to be incremented for 6, 7, 8, etc.<table border=1><td></td><td align=center>H</td><td align=center>I</td><td align=center>J</td><td align=center>9</td><td>Result</td><td>Result</td><td>Result</td><td align=center>10</td><td>Number</td><td>Names</td><td>Rooms</td><td align=center>11</td><td align=right>1</td><td>=VLOOKUP(F$11,key,2,FALSE)</td><td align=right>=VLOOKUP(F11,key,3,FALSE)</td><td align=center>12</td><td align=right>2</td><td>=INDEX(key,SMALL(IF(key=F$11,ROW(key)ROW(A$11)+1,ROW(A$15)+1),H12),2)</td><td align=right>=INDEX(key,SMALL(IF(key=F$11,ROW(key)ROW(A$11)+1,ROW(A$15)+1),H12),3)</td><td align=center>13</td><td align=right>3</td><td>=INDEX(key,SMALL(IF(key=F$11,ROW(key)ROW(A$11)+1,ROW(A$15)+1),H13),2)</td><td>=INDEX(key,SMALL(IF(key=F$11,ROW(key)ROW(A$11)+1,ROW(A$15)+1),H13),3)</td><td align=center>14</td><td align=right>4</td><td>=INDEX(key,SMALL(IF(key=F$11,ROW(key)ROW(A$11)+1,ROW(A$15)+1),H14),2)</td><td>=INDEX(key,SMALL(IF(key=F$11,ROW(key)ROW(A$11)+1,ROW(A$15)+1),H14),3)</td><td align=center>15</td><td align=right>5</td><td>=INDEX(key,SMALL(IF(key=F$11,ROW(key)ROW(A$11)+1,ROW(A$15)+1),H15),2)</td><td>=INDEX(key,SMALL(IF(key=F$11,ROW(key)ROW(A$11)+1,ROW(A$15)+1),H15),3)</td></table>John ... I float in liquid gardens
UTC 7ąDS

20040205, 10:17 #3
 Join Date
 Jan 2002
 Location
 The Hague, Netherlands
 Posts
 283
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Vlookup (2000)
Also...
A1:C6 houses the sample of interest:
{"Room #'s","Name","Key #'s";2,"John",1006;5,"Mark",1003;10,"Ken",1006;15, "Joe",1007;18,"Mike",1003}
In E1 enter: Key# (jus a label)
In E2 enter: Pos (just a label)
In E3 enter & copy down:
=IF(COUNTA($E$2:E2)<=$F$2,MATCH($F$1,INDEX(C:C,N(E 2)+1):INDEX(C:C,MATCH(9.99999999999999E+307,C:C)), 0)+N(E2),"")
In F1 enter: 1006 (which is a key#)
In F2 enter:
=COUNTIF(C:C,F1)
In F3 enter & copy down:
=IF(N($E3),INDEX(B:B,$E3),"")
In G3 enter & copy down:
=IF(N($E3),INDEX(A:A,$E3),"")
See the attachment.Microsoft MVP  Excel