# 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:Worksheet1Column A Column Bcan-1letter-4apple-94litter-0nuts-462Worksheet2Column A Column Bapple-94 4nuts-462 1Goal Worksheet:Worksheet1Column A Column Bcan-1letter-4apple-94 4litter-0nuts-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.

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

#1
February 5, 2015 at 09:33:40
 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.MIKEhttp://www.skeptic.com/