Cross Reference in Two Files

[jarett]

I am sure this is a routinely run task but I am not sure the correct terminology of the task to search on it. I have have two different files, file "abc" has column A as "UPC" and column "E" as inventory qty. In file "#2" I have column "C" as "UPC", I want to take the column "E" from file "abc" and import it into file "#2" in a new column next to the corresponding "UPC" record. Is this called a look up? Can this task be automated in a macro?

 

[Candyman8019]

I think the formula you're looking for is index/match. It can be done via formula, or macro/vba.

Here's a link to the vba route: Translate index/match function to vba code

 

[jarett]

I think the formula you're looking for is index/match. It can be done via formula, or macro/vba.

Here's a link to the vba route: Translate index/match function to vba code

I am looking at the last post with the formula, but don't quite understand how it is looking at two different files, does this only work if it is 2 different sheets and not separate files?

Oh, and I have no experience in VBA, all my previous attempts have been unsuccessful.

 

[Candyman8019]

In the example in that link:

test = Application.WorksheetFunction.Index(Sheets("Workers List").Range("C3:C13"), Application.WorksheetFunction.Match(1, Sheets("Workers List").Range("D3:D13"), 0), 1)

referencing another file would look something like:

test = Application.WorksheetFunction.Index(Workbooks("book1.xlsx").Sheets("Workers List").Range("C3:C13"), Application.WorksheetFunction.Match(1, Workbooks("book2.xlsx").Sheets("Workers List").Range("D3:D13"), 0), 1)

 

[Candyman8019]

One thing to note...for this to work for me, I had to have the other workbooks open.

 

[jarett]

I struggled with your solution so I found another possible way, I can understand this formula better but, it doesn't work. Do you see any issues?

=INDEX([LAPCO_INV.csv]!$A$2:$E$10000,MATCH(C1,[LAPCO_INV.csv]!$A$2:$A$10000,0),2)

 

[Candyman8019]

The format of the formula looks fine. You may need to convert your lapco file to an excel file rather than csv. I'd give that a try.

 

[jarett]

I cant figure out why I get this error.

 

[Candyman8019]

I wonder if the cell has some odd format to it. Try copying that formula and paste it into the formula bar in a different cell.

 

[jarett]

No luck, I have both files open, would it have something to do with the way I am referencing the other file?