Cross check two different spreadsheets and transfer value from one to the other

Hi,

I have two separate spreadsheets with lists of email addresses. Each spreadsheet has email addresses in column A, followed by different properties in subsequent columns to Column D. I'm looking for a way in excel to search one spreadsheet to see if the same email is present in both spreadsheets. Then, if it finds a match, I'm hoping to transfer the cell contents from one of the properties to the other spreadsheet.

In excel terms its something like this.

If an email address in Spreadsheet#1 cell A2 is found in the range A1:A10000 of Spreadsheet#2, then grab the information relevant to that email address from column B in Spreadsheet #2, and add it to E2 in Spreadsheet #1.

I'm sorry that I couldn't find a better way to explain this, but I'm fairly new to excel!

Thank you for your help!

Try this:

'Run this code in Spreadsheet#1

Please Login or Register  to view this content.

Thanks for your help jewelsharma. I ran the line of code, but kept getting Runtime error '1004' on the line If .Range("B"..... saying that it was unable to get the Vlookup property of the WorksheetFunctionClass. I attached an example spreadsheet (without actual email addresses) to see if we can sort it with references that are a little less abstract.

So for example: I'm looking to see if the email address in A2 of 'Email List 1' exists in the range (A2:A5)of sheet 'Email List 2'. If so, I want it to put the corresponding "Car" value for that email address into column F of 'Email List 1'.


Test Spreadsheet.xlsx

Looks like this is a Vlookup problem, I think I figured it out.

Hi thewhawk,

Notes:

• i renamed the files as Test1 and Test2.
• i renamed worksheets as "Sheet1" in both the files.
• the code is to be run from Test1 workbook.

Try this modified code:

Please Login or Register  to view this content.

This works fine when I tested. Let me know if this worked for you.

Warm regards,
Jewel