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

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

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