title: Vlookup with a named range
body: <p>I have a variable which takes the name of a workbook. I will be working through a list of these workbooks. For example,</p>
  
  <pre><code>dim unit_index as variant
  
  unit_index = "unit_20444b_201608.xls"
  </code></pre>
  
  <p>I want to use a <code>vlookup</code> to return a value from the workbook assigned to <code>unit_index</code>. So when I enter the <code>vlookup</code> formula manually and record the macro I get the code below:</p>
  
  <pre><code>ActiveCell.FormulaR1C1 = _
      "=VLOOKUP(RC[-1],unit_20444b_201608.xls!alpha_lookup,2,FALSE)"
  </code></pre>
  
  <p>I want the workbook name to change dynamically so I want the code to look something like this:</p>
  
  <pre><code>ActiveCell.FormulaR1C1 = _
      "=VLOOKUP(RC[-1],**unit_index**!alpha_lookup,2,FALSE)"
  </code></pre>
  
  <p><code>alpha_lookup</code> is the named range in <code>unit_index</code>.</p>
  
  <p>So the workbook name would change as <code>unit_index</code> is updated. How can I make this work?</p>