Vlookup between multiple workbooks to pull in data value

[koppahollic]

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

 

[Anthony47]

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:

=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]

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:

=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]

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:

=IFERROR(MainFormula,TheValueIfMainFormulaFails)

In this case, for example:

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

or maybe

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

Bye