title: VBA Reference Tables in Index Match
body: <p>EDIT:  I am getting  a <code>#VALUE!</code> error when I try to run this function. It works if I just do the <code>If statement</code> so the logic seems good. The issue is recreating the <code>INDEX MATCH MATCH</code> formula. I am trying to reference column 15 of a table called 'Headcount' and find the corresponding result in the 'Region' column.</p>
  
  <p>tblHeadcount:</p>
  
  <pre><code>Name          Region    
  Bob           001    
  Jake          003    
  Bill          001
  </code></pre>
  
  <pre><code>Function CO_GLREFORM(CellRef1 As Range, CellRef2 As Range, CellRef3 As Range) As String
      Dim tblHeadcount As ListObject
      Dim matchColResult As Long
      Dim matchRowResult As Long
      Dim indexResult As Variant
  
      '------THIS IS THE CODE THAT CREATES #VALUE! ERROR
      'Set tblHeadcount = ActiveSheet.ListObjects("Headcount")
      'matchColResult = WorksheetFunction.Match(CellRef3, tblHeadcount.ListColumns(15).DataBodyRange, 0)
      'matchRowResult = WorksheetFunction.Match(tblHeadcount.HeaderRowRange(16), tblHeadcount.TotalsRowRange, 0)
      'indexResult = WorksheetFunction.Index(tblHeadcount.DataBodyRange, matchColResult, matchRowResult)
  
      If UCase(CellRef1) = "CONFERENCE" Or UCase(CellRef1) = "TRAINING" Then
         '-----THIS IS THE CODE GENERATED (WITH ADJ) FROM THE MACRO RECORDER------ 
         ActiveCell.FormulaR1C1 = _
          "=CellRef2 &amp;""-""&amp;INDEX(Headcount,MATCH(CellRef3,Headcount[Name Verification],0),MATCH(Headcount[[#Headers],[Region]],Headcount[#Headers],0))&amp;""-7210.0100"""
         '-----THIS IS MY ATTEMPT TO RECREATE THE FORMULA 
         'CO_GLREFORM = CellRef2 &amp; "-" &amp; indexResult &amp; "-7210.0100"
      Else
          ActiveCell.FormulaR1C1 = _
          "CO_GLREFORM = CellRef2 &amp;""-""&amp;INDEX(Headcount,MATCH(CellRef3,Headcount[Name Verification],0),MATCH(Headcount[[#Headers],[Region]],Headcount[#Headers],0))&amp;""-7210.0105"""
          'CO_GLREFORM = CellRef2 &amp; "-" &amp; indexResult &amp; "-7210.0105"
      End If
  
      '--------THIS IS THE FORMULATE I AM REPLICATING----------------
      'If(OR(CellRef1="CONFERENCE",CellRef1="TRAINING"),CellRef2&amp;"-"&amp; _
          INDEX(Headcount,MATCH(CellRef3,Headcount[Name Verification],0), MATCH(Headcount[[#Headers],[Region]],Headcount[#Headers],0))&amp;"-7210.0100",CellRef2&amp;"-"&amp; _
          INDEX(Headcount,MATCH(CellRef3,Headcount[Name Verification],0),MATCH(Headcount[[#Headers],[Region]],Headcount[#Headers],0))&amp;"-7210.0105")
  
  End Function
  ``
  </code></pre>