• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 286
  • Last Modified:

VLookup Assistance

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.


1 Solution
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.
kgerbChief EngineerCommented:
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.


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

here's a example

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

FrankkadafAuthor Commented:
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!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now