comparing two different columns and copy field from 3rd column

I have tried using vlookup for this but it is not returning the currect field. I have two different excel files. I need to compare phone numbers from one column in file 1 to all of the different worksheets column C in file 2. If file 2 has the same phone number from file 1, then copy the entry from column L on that row to column F in file 1.

More clarification:

File 1:
Compare all phone numbers in column A to column C in all worksheets in File 2.

File 2:
If a matching phone number is found from column A in file 1 and any column C in file 2, then copy the number in column L on the same matching row from file 2 into the same matching row of the original phone number in file 1 into column F.

Let me know if there is a function that will do this or if you need more clarification.

Thanks!

bkl99,

This should work:

Please Login or Register  to view this content.

Notes:
-Formula goes in column L of each worksheet
-Update the C:\Test Folder to be the location of the workbook that contains the phone numbers
-Update Phone Number.xls to be the name of the workbook that contains the phone numbers
-Update Sheet1 to be the name of the sheet that contains the phone numbers
-Update the $A$2:$F$10 to be the range that contains the phone number to lookup and the corresponding row in F to return. Keep the $ symbols.

Hope that helps,
~tigeravatar

bkl99,

Or are you trying to go the other direction? Compare the File1 to each worksheet in File2 and return the result from File2, column L for each worksheet?

~tigeravatar

bkl99,

You can use this macro to accomplish what you're looking for. Some notes about the macro:

• It requires that only File1 be open, viewing the worksheet with the phone numbers you want to look for in column A
• When you run the macro, you will be prompted to open a workbook. The workbook that should be opened is File2

Here's the code

Please Login or Register  to view this content.

Hope that helps,
~tigeravatar