Using VLOOKUP with two different Excel Files (workbooks)

Question

I am having trouble using VLOOKUP within two different excel files (Not two sheets).

One File contain the terminal Log with dates, Product name and Output (Calculated).
The other file contain total analyzed about the same product name and all.

I want to include those output (Calculated) from 1st workbook to 2nd workbook. Note that they contain same product name under title "商品名".

What I have tried:

 VLOOKUP([1stfile!productname,1stfile!$productname$1:ProductnameEndcolumn,5,0)

All i get is same number "616" in the whole column.

Actual Code:
=VLOOKUP(Trn.txt!$I$1,Trn.txt!$I$1:$M$7216,5,0)

Here Trn.txt is firstfile

I1 is the column where it contains product name
m7216 is End point of product name column
5 is the Output(Calculated)column which I want
0 False = Exact Match

I only get the product value of 1st product which is 616. And when I Ctrl+click drag to bottom, every cell has same value 616.

ashleedawg ashleedawg · Accepted Answer · 2018-04-10T02:35:50

I have a terrible memory, so I often forget how to reference different cells/worksheets/files.

So, I just let Excel figure it out for me...

Ah yes, I remember now. The correct syntax is:

=[WorkbookName]SheetName!CellReference

for example:

='C:\myPath\[myWorkbook.xlsm]Sheet1'!$A$1

I trust you'll be able to adapt that to your VLOOKUP without issue.

Something else that may be of benefit to you is the "Crash Course" on Relative vs Absolute cell references at the bottom of my answer here.

Using VLOOKUP with two different Excel Files (workbooks)

2 Answers