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.

Solved

Posted on 2011-10-24

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 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.

Thanks!

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

John

Sara

Aaron

Tim

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 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

John

Sara

Aaron

Tim

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

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.

Thanks!

5 Comments

=vlookup(A1,Sheet1!A1:D4,4

Kyle

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)",

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

Reg

This article will show you how to use shortcut menus in the Access run-time environment.

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

Connect with top rated Experts

**17** Experts available now in Live!