Vlookup between multiple workbooks to pull in data value

koppahollic

New Member
Joined
Oct 26, 2020
Messages
6
Office Version
  1. 2013
Platform
  1. Windows
Hi there. I'd appreciate any insight into the issue i'm running into outlined below. thanks.

Scenario: I have 2 files both with the same unique user id. I want to import file 2 to update my CRM because it has additional data values. I need to match the files on the user id but i can't figure out how craft a formula when the vlookup matches on the user id to also pull the corresponding email address with it in a new column from file 1 into file 2.

File 1 data columns
  • user id (column a)
  • email address (column b)
File 2 data columns
  • user id (column a)
  • multiple columns with additional data but no email address
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

Anthony47

Well-known Member
Joined
Mar 29, 2006
Messages
2,147
Open both your files; select the position where inserting your formula; start writing your formula: "=VLOOKUP(ValueToLook,"; now select the other file (File 1, I guess), the correct sheet, and select with the mouse the area where look. This will automatically fill the formula with the write information; complete the formula with the Index (which column to get), and the Interval (False for Exact match, I guess); Enter to finish.
Your formula will look like this:
Code:
=VLOOKUP(J4,'[FileName.xlsm]SheetName'!$A$1:$L$25,10,FALSE)
When you close "File 1" the formula will show not only the FileName but its full path, as the formula can retrieve data even if the target file is closed. Btw this is a problem if you have many VLOOKUP to a closed file, because their recalculation could require seconds to be completed.

Bye
 

koppahollic

New Member
Joined
Oct 26, 2020
Messages
6
Office Version
  1. 2013
Platform
  1. Windows
Open both your files; select the position where inserting your formula; start writing your formula: "=VLOOKUP(ValueToLook,"; now select the other file (File 1, I guess), the correct sheet, and select with the mouse the area where look. This will automatically fill the formula with the write information; complete the formula with the Index (which column to get), and the Interval (False for Exact match, I guess); Enter to finish.
Your formula will look like this:
Code:
=VLOOKUP(J4,'[FileName.xlsm]SheetName'!$A$1:$L$25,10,FALSE)
When you close "File 1" the formula will show not only the FileName but its full path, as the formula can retrieve data even if the target file is closed. Btw this is a problem if you have many VLOOKUP to a closed file, because their recalculation could require seconds to be completed.

Bye
i understand this part (telling me if there is a match across both files). is it possible though to add an IF statement in it? that way if there is a match, it will populate with the corresponding email address (column e) from the other file? this is my formula currently: =VLOOKUP([@[ lead_person_id ]],[Book1]Sheet1!$A:$A,1,FALSE)
 

Anthony47

Well-known Member
Joined
Mar 29, 2006
Messages
2,147
Not sure I undestood your question... If you wish avoiding to show the #N/A in case the VLookup fails then you can use the function ISERROR.
General sysntax:
VBA Code:
=IFERROR(MainFormula,TheValueIfMainFormulaFails)
In this case, for example:
Code:
=ISERROR(VLOOKUP(J4,'[FileName.xlsm]SheetName'!$A$1:$L$25,10,FALSE),"")

or maybe
Code:
=ISERROR(VLOOKUP(J4,'[FileName.xlsm]SheetName'!$A$1:$L$25,10,FALSE),"No Email")
Bye
 

Watch MrExcel Video

Forum statistics

Threads
1,129,745
Messages
5,638,114
Members
417,009
Latest member
Emily00028

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top