VLookup Assistance

Posted on 2011-10-24
Last Modified: 2012-05-12
Ok, my first question was answered, it is below, but I have added another few columns, and I need to pull that data over as well.  Here is the first scenario:

I have 2 sets of data in 2 worksheets.  1 large set with names (column A) and numbers (column B); and the second just has names (column A).  Most, but not all of the names from the second set of data are in the first set of data.

I would like to extract the numbers for just the names listed in the second set of data.  I tried the "lookup" function, but if a name is on the 2nd set of data, it assigns the lowest number from the range it thinks it belongs to.

For example

List 1
John     18
Frank     21
Sara      22
Aaron    15

List 2

Can I get the numbers for the matches, and if there is no match, no data, or #value is returned.

Can anyone get me the correct formula with syntax please?

I now would like to get all columns (after the 18, 21, etc...) from the large data set to the small one, and dragging the formula does not work.

This was the last solution:

assuming list 1 is on sheet1 from A1:B4
and list 2 is on sheet2 from A1:A4

On cell B1 on sheet2, add =vlookup(A1,Sheet1!A1:B4,2,0)
and fill down B1 to B4 on sheet 2.

What do I need to add to allow all columns after the name to get to the smaller data set.


Question by:Frankkadaf
    LVL 10

    Expert Comment

    copy the formula to the next column, changing the 2 before the ,0 at the end to 3, and then 4 for the next column etc.
    LVL 12

    Expert Comment

    You need to change the size of your table A1:D4 for instance.  Then you need to change the column returned from 2 to 3 or 4.  Whichever column you want returned from your table.  For example to return the 4th column you would do.


    LVL 19

    Accepted Solution

    vlookup only returns one column at a time so it can get tedious when doing lots of horizontal columns because you have to change the returned column each time.

    One way to get round this is to use "column()" to return the number of the column you want to get:-
    Column A is 1, column b is 2 and so on. You need to add or subtract the appropriate number of columns unless you are returning the data into the same column in the short list (eg: if you are returning sheet 1 column c to sheet 2 column c "=vlookup(a1, sheet1!a1:D4,column(),0)", now as you drag the formula along the 'column()' will automatically correct itself.

    Another, more robust way, is to put a header on each column and use 'match' in the formula to pick which column you are returning - come back if you think you would prefer that

    LVL 19

    Expert Comment

    here's a example

    If you want it to deal with errors, missing items etc let me know


    Author Closing Comment

    The column() instead of the "2" did the job at the end of the formula, as far as errors, the "N/A" is great, that is what I want it to do so the missing data is easily identified.

    Thanks Reg!

    Featured Post

    Live: Real-Time Solutions, Start Here

    Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

    Join & Write a Comment

    This article will show you how to use shortcut menus in the Access run-time environment.
    Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
    This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now