title: Excel / VBA - Index Match function using Dynamic Ranges
body: <p>How to effectively use an <code>Index/Match</code> formula in VBA?</p>
  
  <p>Background:  I have a worksheet that relies heavily on the use of a formula that retrieves an output based on matching a specific name to its name range as well as a specific date to its date range.  </p>
  
  <pre><code>=INDEX(OutputRange,MATCH(1,(Name=NameRange)*(Date=DateRange),FALSE),1)
  </code></pre>
  
  <p>Additionally, there is a hard-coded VBA sub that produces the same output</p>
  
  <pre><code>Sub ExampleHardCode() 
  Dim Result As Variant
    Result = Evaluate("INDEX($C$4:$C$13,MATCH(1,($G$6=$A$4:$A$13)*($G8=$B$4:$B$13),FALSE),1)")
    ActiveCell.Value = Result
  End Sub
  </code></pre>
  
  <p>Question: I’d like to produce a function that returns the same output as the above options but allows the user to (i) select the Name and Date values by referencing respective cells and (ii) select each range (name range, date range and output range).  Essentially using =examplefunction(name value, name range, date value, date range, output range) in excel.</p>
  
  <p>I’ve tried a number of different solutions but with no success. Below is an example of what I've tried so far, I think there is an issue with the match portion as even when I try to set the ranges (with hardcoded ranges) it returns an error.  </p>
  
  <pre><code>Function TestIndexMatch1(NameVal As Variant, DateVal As Date)
  
  Dim NameRng As Range
  Dim DateRng As Range
  Dim OutputRng As Range
  Dim Var1 As Variant  'should this be a range or integer?
  Dim Result As Variant 
  
  Set NameRng = Range("$A$4:$A$13")
  Set DateRng = Range("$B$4:$B$13")
  Set OutputRng = Range("$C$4:$D$13")
  
  With Application.WorksheetFunction
      Var1 = .Match(1, (NameVal = NameRng) * (DateVal = DateRng), False)
      Result = .Index(OutputRng, Var1, 1)
  End With
  End Function
  </code></pre>
  
  <p>I have an example workbook if it helps I can share.  I'm not sure if this is very do-able but if so it'd really help a lot of users that aren't familiar enough with excel to use the index/match excel formula correctly.  Unfortunately for me my excel skills far exceeds my VBA skills.</p>