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]

Give this a try:
=index('[Lapco_inv.xlsx]Sheet1'!$A$2:$E$10000,match(C1,'[Lapco_inv.xlsx]Sheet1'!$A$2:$A$10000,0),2)

Replace Sheet1 with whatever your tab name is.

 

[jarett]

Well a little progress, got a N/A result, some reason it shortens the formula and takes out the tab name when I paste it into the formula bar.
=INDEX(LAPCO_INV.xlsx!$A$2:$E$10000,MATCH(C1,LAPCO_INV.xlsx!$A$2:$A$10000,0),2)

I might be able to dissect it now that its a real formula.

 

[Candyman8019]

Okay, give this a try. Have both files open. When editing the formula, highlight this piece:
LAPCO_INV.xlsx!$A$2:$E$10000
then go to your lapco sheet and select range A2:E10
hit enter.
Your formula should now reference the correct sheet and you can manually update your range to E10000.

 

[jarett]

Okay, give this a try. Have both files open. When editing the formula, highlight this piece:
LAPCO_INV.xlsx!$A$2:$E$10000
then go to your lapco sheet and select range A2:E10
hit enter.
Your formula should now reference the correct sheet and you can manually update your range to E10000.

ok tried that and this is my formula now after manually updating the range.

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

So the values I am trying to "match" up are UPC codes and excel is storing them in the weird scientific format "8.41E+11", thats how they look in the cell but the cell format is set to general, could this be a reason as well?

 

[Candyman8019]

not quite there. Change LAPCO_INV.xlsx!$A$2,LAPCO_INV.xlsx!$E$10000 back to
LAPCO_INV.xlsx!$A$2:$E$10000
It could be the cell format. Maybe try changing them to a text field in both workbooks.

 

[jarett]

Still error, maybe I have the formula all wrong, because I am trying to return the value in "LAPCO_INV" column E (QUANTITY), in "UPCTEST" file in the same row with the corresponding UPC. Which part of the formula is the return value when the statement is true?

 

[Candyman8019]

Can you use the xl2bb addin from this site and post samples of both sheets?

 

[jarett]

Well, I finally got it to work,
=INDEX(LAPCO_INV.xlsx!$A$2:$E$10000,MATCH(C4154,LAPCO_INV.xlsx!$A$2:$A$10000,0),5)

A couple things wrong on my side, had the wrong column reference for the result and the biggest issue was all the UPC numbers being supplied were missing the last digit, that's why I kept getting the N/A.

Thanks for the help!