Solved Look for matches between 2 worksheets in Excel 2010

February 5, 2015 at 06:13:18
Specs: Windows 64
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-462

Worksheet2
Column A Column B
apple-94 4
nuts-462 1


Goal Worksheet:
Worksheet1

Column A Column B
can-1
letter-4
apple-94 4
litter-0
nuts-462 1

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


See More: Look for matches between 2 worksheets in Excel 2010

Report •

#1
February 5, 2015 at 09:33:40
✔ Best Answer
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-462    

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

You 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

http://www.skeptic.com/


Report •
Related Solutions


Ask Question