title: Referencing a table in different workbook using IndexMatch or Vlookup only works when WB open
body: <p>Excel 2013 64bit, win 7</p>
  
  <p>Goal = Use a Table name reference from a closed workbook in formula with Index Match (or vLookup if necessary workaround). </p>
  
  <p>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)</p>
  
  <p>Works only when workbook is open, when closed it shows #REF!
  <code>=INDEX('C:\Vendors.xlsm'!VendName[[#All],[Qty]],MATCH(A1,'C:\Vendors.xlsm'!VendName[[#All],[SKU]],0))</code></p>
  
  <p>With vlookup, same thing, it only works when workbook is opened. </p>
  
  <pre><code>=VLOOKUP(A1,'C:\Vendors.xlsm'!VendName[#All],3,0)
  </code></pre>
  
  <p>However if I use named range instead of table it will work!</p>
  
  <pre><code>=VLOOKUP(A1,'C:\Vendors.xlsm'!VendName,3,0)
  </code></pre>
  
  <p>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. </p>
  
  <p>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?</p>
  
  <p>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. </p>
  
  <pre><code>=INDEX(ExcelFile.xlsx!NamedRange,MATCH(F4,ExcelFile.xlsx!NamedRange,0))
  </code></pre>
  
  <p>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...</p>
  
  <p>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). </p>
  
  <blockquote>
    <p>=INDEX(NameRange,MATCH(H10,NameRange,0),3)</p>
  </blockquote>