Referencing a table in different workbook using IndexMatch or Vlookup only works when WB open

Question

Excel 2013 64bit, win 7

Goal = Use a Table name reference from a closed workbook in formula with Index Match (or vLookup if necessary workaround).

Seems simple enough, but none of these are working when I recalculate after closing the referenced Tables workbook. This one should work and I tried a lot of other things but no success. (EDIT: My understanding is this is impossible to reference a table in a closed workbook? Going to move on to bottom solution)

Works only when workbook is open, when closed it shows #REF! =INDEX('C:\Vendors.xlsm'!VendName[[#All],[Qty]],MATCH(A1,'C:\Vendors.xlsm'!VendName[[#All],[SKU]],0))

With vlookup, same thing, it only works when workbook is opened.

=VLOOKUP(A1,'C:\Vendors.xlsm'!VendName[#All],3,0)

However if I use named range instead of table it will work!

=VLOOKUP(A1,'C:\Vendors.xlsm'!VendName,3,0)

So why not use name range with Index Match? I couldnt figure out how to tell Index match to use a specific column of a named range like I do a table. ie follow the table name with [[#All],[SKU]]. Also I find it hard to believe you cant reference a table in a closed workbook, I must be missing the proper solution.

What am I doing wrong in the first formula so that it will not work when table WB is closed? And how would I use a named range but tell it to reference the 3rd row of a named range within Index Match formula?

Update: I believe I need to ditch the external table and use the external regular NameRanges only, but how do I reference a name range/2nd column.

=INDEX(ExcelFile.xlsx!NamedRange,MATCH(F4,ExcelFile.xlsx!NamedRange,0))

In this example, the Named range is 5 columns wide, how do I tell it to match with the 1st row and reference the 3rd. Ie =INDEX(ExcelFile.xlsx!NamedRange,Row(2) or something...

I cant figure out how to tell Index match to use a specific column of a named range. In the below: Name Range refers to column A:D. Hardcode to match data from column 3 at the end there is perfectly fine. What make this formula fail though is the second use of NameRange. The formula needs to reference column B (2nd column of the range NameRange). How do I force that? Something like NameRange[Column2] (I made up the format to express the goal).

=INDEX(NameRange,MATCH(H10,NameRange,0),3)

Answer 1

You could perform an index(data_table,match1,match2), which pulls match1 by comparing the search term with the reference column, and which pulls match2 by comparing the search term with the reference row.

ie: Assume your reference column on the data table workbook is A:A. Assume your reference row (for headers) is 1:1.

Set 3 named ranges:

(1) A1:Z100 = "FullData" showing the full data table as a 2D range. (2) A:A = "ItemIndex" showing only the reference column. (3) 1:1 = "HeaderIndex" showing only the refrence row.

Your formula to reference this in an your results workbook would be:

=INDEX('C:\Vendors.xlsm'!FullData,MATCH($A1,'C:\Vendors.xlsm'!ItemIndex,0),MATCH("HEADER NAME TO MATCH",'C:\Vendors.xlsm'!HeaderIndex,0))

There may be some broader issues with using this to reference a closed book, but I'm not very familiar with that general process, so someone may have thoughts on a better way for you to do this overall. This answers your initial question to do it with your method, at least.

EDIT

As you have 20+ tables, and the headers are always in the same order, you can simply use your vlookup as proposed like this; will prevent you from needing to declare 3 Name Ranges for each tab; instead you will only need to create one Named Range per tab, like so:

=vlookup(A1,'C:\Vendors.xlsm'!FullData,5)

This hardcodes to pull in the 5th column as you said the headers are the same from table to table and don't change. To instead make it pull from the header you want, create a searching index row in your results tab (let's assume row 1 on sheet 2, with the search term for the header on cell B1):

    =vlookup(A1,'C:\Vendors.xlsm'!FullData,MATCH(B1,Sheet2!1:1,0))

Answer 2

I concluded the following:

1. Cannot reference tables in closed workbooks for Index Match and vLookup. Use simple Name Ranges.

2. Here is how to use Index Match with NameRanges that contain multiple columns. =INDEX(NameRange,MATCH(H10,INDEX(NameRange,0,1),0),3)

The solution to being specific about a return value column for the first NameRange is to add that ending ,3) thus returning the value in the 3rd column.

The solution to being specific about the lookup column to match is instead of just putting the MATCH(A1,NameRange,. use MATCH(A1,INDEX(NameRange,0,1),... This tells it to use the first column in that Named Range.

This solutions allows you to write one formula for a standard table layout, and simply change the Name for each range and apply across. I then was able to use this with Indirect so that the formula throughout the sheet can reference a helper cell on top so it never has to be changed when adding more vendors.