index and match to external file

Question

I am trying to create a function that preforms an index and match (vlookup) to another file. I have a file that is zipcodes in ColA with a relative cost factors in ColB. I often find myself copying and pasting that sheet into my workbooks and doing a vlookup. is there a way that i can create a "Function" where the only argument is the zipcode and it returns the cost factor for that zipcode without having to copy and re-write an index match function every time.

Thanks!

Answer 1

The VLOOKUP function can reference a closed workbook.

Open both workbooks and build your first VLOOKUP or INDEX/MATCH function pair.

=VLOOKUP(A2, '[extzips.xlsb]Sheet1'!$A:$B, 2, FALSE)
=INDEX([extzips.xlsb]Sheet1!$A:$A, MATCH(B2, [extzips.xlsb]Sheet1!$B:$B, 0))

Close the workbook with the zip references. The formula(s) will adjust to something like,

=VLOOKUP(A2, 'C:\Users\user\Documents\[extzips.xlsb]Sheet1'!$A:$B, 2, FALSE)
=INDEX('C:\Users\user\Documents\[extzips.xlsb]Sheet1'!$A:$A, MATCH(B2, 'C:\Users\user\Documents\[extzips.xlsb]Sheet1'!$B:$B, 0))

Those formulas can be copied or filled to new locations and will return the appropriate values.