Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 273
  • 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!

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

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