Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Mar 2004
    Location
    London, Gtr London, England
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    simple vlookup query (WinXP Excel 2002)

    This is so basic, but I cant see it yet. I have a list of 23000 students, each has a Student Number (SN) and a Candidate Number (CN) for exams. For anonymity these are only matched up after the exam, so I have a separate list of the 450 students who took the exam. I need to match up each SN from the smaller list with the same SN in the big list, find the corresponding CN for that student, and place the CN next to the SN in the small list. I think vlookup will do this but dont know how. Please help, thanks Frank

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: simple vlookup query (WinXP Excel 2002)

    Hi,
    Assuming that your large list has the SN column to the left of the CN column, then yes, vlookup is what you need. Assuming your SN is in cell A1 in the small list and the big list is on Sheet2 in cells A1:B23000, the formula would be:
    <code>=vlookup(A1,Sheet2!$A$1:$B$23000,2,false)</code>
    Note: the 2 represents which column you want to return data from (I have assumed your large list just has 2 columns) and the False means that you only want to find exact matches.
    Hope that helps but if you need any clarification, please post back.
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    3 Star Lounger
    Join Date
    Mar 2004
    Location
    London, Gtr London, England
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: simple vlookup query (WinXP Excel 2002)

    Thanks Rory, that look good.
    The two list are in different workbooks, so I need to refer to the large list somehow. Can you tell me how to do that? Then I think it will work.

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: simple vlookup query (WinXP Excel 2002)

    if you have both workbooks open, the easiest way is to start the formula with
    <code>=vlookup(A1,</code>
    and then switch to the other workbook and select the range, then type
    <code>,2,false)</code>
    and press Enter. You should end up with something that looks like: <code>=VLOOKUP(A1,[Workbook2.xls]Sheet1!$A$1:$B$23000,2,FALSE)</code>.
    Note: when the other workbook is closed, the formula will show the entire path to it, rather then just its name.
    HTH.
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    3 Star Lounger
    Join Date
    Mar 2004
    Location
    London, Gtr London, England
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: simple vlookup query (WinXP Excel 2002)

    Thank you! Perfect

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: simple vlookup query (WinXP Excel 2002)

    If the other workbook is open:
    =VLOOKUP(A1,'[BookName.xls]Sheet2'!$A$1:$B$23000,2,FALSE)

    If closed:
    =VLOOKUP(A1,'C:Path[BookName.xls]Sheet2'!$A$1:$B$23000,2,FALSE)

    Change the names as desired...

    Steve

Posting Permissions

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