Excel / VBA - индексная функция соответствия с использованием динамических диапазонов

Как эффективно использоватьIndex/Match формула в VBA?

Предыстория: у меня есть лист, который в значительной степени опирается на использование формулы, которая извлекает выходные данные на основе сопоставления определенного имени с его диапазоном имен, а также конкретной даты с его диапазоном дат.

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

Кроме того, есть жестко сабвуфер VBA, который производит тот же вывод

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

Вопрос: Я хотел бы создать функцию, которая возвращает тот же вывод, что и вышеуказанные параметры, но позволяет пользователю (i) выбирать значения имени и даты, ссылаясь на соответствующие ячейки, и (ii) выбирать каждый диапазон (диапазон имен, диапазон дат и диапазон выхода). По сути, используется = examplefunction (значение имени, диапазон имен, значение даты, диапазон дат, диапазон вывода) в Excel.

Я пробовал несколько разных решений, но безуспешно. Ниже приведен пример того, что я пробовал до сих пор, я думаю, что есть проблема с частью совпадения, так как даже когда я пытаюсь установить диапазоны (с жестко заданными диапазонами), он возвращает ошибку.

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

У меня есть пример рабочей книги, если она поможет, я могу поделиться. Я не уверен, что это очень выполнимо, но если это так, то это действительно поможет многим пользователям, которые недостаточно знакомы с Excel, правильно использовать формулу index / match excel. К сожалению для меня, мои навыки превосходства намного превышают мои навыки VBA.

Ответы на вопрос(1)

Ваш ответ на вопрос