Results 1 to 3 of 3

Thread: Vlookup (2000)

  1. #1
    2 Star Lounger
    Join Date
    Dec 2002
    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

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Salt Lake City, Utah, USA
    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 Ctrl-Shift-Enter. 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

  3. #3
    3 Star Lounger
    Join Date
    Jan 2002
    The Hague, Netherlands
    Thanked 0 Times in 0 Posts

    Re: Vlookup (2000)


    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:


    In F3 enter & copy down:


    In G3 enter & copy down:


    See the attachment.
    Microsoft MVP - Excel

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts