Excel / VBA - Index Match function using Dynamic Ranges

Question

How to effectively use an Index/Match formula in VBA?

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.

=INDEX(OutputRange,MATCH(1,(Name=NameRange)*(Date=DateRange),FALSE),1)

Additionally, there is a hard-coded VBA sub that produces the same output

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

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.

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.

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

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.

Answer 1

To use array formulas within VBA code set ReferenceStyle for Application object to xlR1C1 (temporarily, only during your function is executed). Finally call Evaluate to get the result of the formula.

Private Const TEMPLATE As String = "=INDEX({0},MATCH(1,({1}={2})*({3}={4}),{5}))"
Private Const MATCH_TYPE = 0

Public Function TestIndexMatch1(ByRef outputRange As Range, _
                                ByRef nameCriteria As Range, _
                                ByRef dateCriteria As Range, _
                                ByRef nameRange As Range, _
                                ByRef dateRange As Range)

    On Error GoTo Err_Handler
    Err.Number = 0

    Dim originalReferenceStyle
    originalReferenceStyle = Application.ReferenceStyle
    Application.ReferenceStyle = xlR1C1

    Dim myFormula As String
    myFormula = Replace(TEMPLATE, "{0}", outputRange.Address())
    myFormula = Replace(myFormula, "{1}", nameCriteria.Address())
    myFormula = Replace(myFormula, "{2}", nameRange.Address())
    myFormula = Replace(myFormula, "{3}", dateCriteria.Address())
    myFormula = Replace(myFormula, "{4}", dateRange.Address())
    myFormula = Replace(myFormula, "{5}", MATCH_TYPE)

    TestIndexMatch1 = Application.Evaluate(myFormula)

Err_Handler:
    If (Err.Number <> 0) Then MsgBox Err.Description
    Application.ReferenceStyle = originalReferenceStyle
End Function

And so it looks on sheet: