I've been attempting to write a macro to do a comparison with a copy function when a match is found. Hopefully this helps explain it: Situation:

Worksheet1 column A is always the same and has the same values (can-1, letter-4, etc).

Worksheet2 is imported via a macro and always has a different variety of the values of worksheet1 column A (In column A of Worksheet 2) plus additional column B with a quantity.Worksheet1 has a set number of 500 rows.

Worksheet2 may contain anywhere from 5 to 200 rows.Both worksheet values start on the same column row A5. (If that makes a difference)

Goal:

I'm trying to search Worksheet2 Column A for matches with Worksheet1 Column A and when there is a match copy the quantity from Worksheet2 Column B to Worksheet1 Column B.Example:

Worksheet1

Column A Column B

can-1

letter-4

apple-94

litter-0

nuts-462Worksheet2

Column A Column B

apple-94 4

nuts-462 1

Goal Worksheet:

Worksheet1Column A Column B

can-1

letter-4

apple-94 4

litter-0

nuts-462 1I hope I've given enough details. Any assistance is greatly appreciated. So far I just keep replicating the #value. So I know I'm missing something.

A =VLOOKUP() should get you what your looking for. With your Sheet 1 like:

A B 1) can-1 2) letter-4 3) apple-94 4) litter-0 5) nuts-462In cell B1 enter the formula:

=IF(ISERROR(VLOOKUP(A1,Worksheet2!$A$1:$B$2,2,0)),"",VLOOKUP(A1,Worksheet2!$A$1:$B$2,2,0))

You should get this:

A B 1) can-1 2) letter-4 3) apple-94 4 4) litter-0 5) nuts-462 1You only gave two rows of data on Sheet 2,

If you have more than two rows of data on Sheet 2 you will need to modify

the formula and change the ranges.You specified the sheet names as Worksheet1 & Worksheet2,

If the sheet names are different you will need to modify the formula and change the

sheet names.See how that works for you.

MIKE

Ask Your Question

Weekly Poll