Option Explicit
Private Const TEMPLATE As String = "=INDEX({0},MATCH(1,({1}={2})*({3}={4}),{5}))"
Private Const MATCH_TYPE = 0
Sub MatchName()
Dim NameValue As Range, DateValuea As Range, ReturnRange As Range
Dim a As Variant
Dim s As Worksheet
With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
.EnableEvents = False
End With
ActiveSheet.DisplayPageBreaks = False
Set s = Worksheets("test")
'Dim namevalue As Range
'Dim DateValuea As Range
'Dim returnrange As Range
Set NameValue = Range(s.Cells(3, 5), s.Cells(6, 5))
Set DateValuea = Range(s.Cells(3, 6), s.Cells(6, 6))
Set ReturnRange = Range(s.Cells(3, 7), s.Cells(6, 7))
' On Error Resume Next
'solution styles
With Application.WorksheetFunction
' Solution option 1
'ReturnRange, Application.WorksheetFunction.match(1, ((s.Cells(3, 1) = NameValue) * (s.Cells(3, 2)) = DateValuea), 0)
' =INDEX({0},MATCH(1,({1}={2})*({3}={4}),{5}))
s.Cells(3, 3) = IndexMatch1(ReturnRange, NameValue, s.Cells(3, 1), DateValuea, s.Cells(3, 2))
' Solution option 2
'' s.Cells(3, 3) = .IfError(.Index(ReturnRange, .match(s.Cells(3, 1) & s.Cells(3, 2), NameValue & DateValuea, 0), 0), "")
''
'' ' Solution option 3
'' s.Cells(3, 3) = .SumProduct((NameValue = s.Cells(3, 1)) * (DateValuea = s.Cells(3, 2)) * ReturnRange)
''
'' ' match funktioner virker heller ikke
'' a = .match(s.Cells(3, 1) & s.Cells(3, 2), NameValue & DateValuea, 0)
'' a = .match(1, (s.Cells(3, 1) = NameValue) * (s.Cells(3, 2) = DateValuea), 0)
End With
End Sub
Public Function IndexMatch1(ByRef outputRange As Range, _
ByRef nameCriteria As Range, _
ByRef nameRange As Range, _
ByRef dateCriteria As Range, _
ByRef dateRange As Range)
Dim myFormula As String
Dim originalReferenceStyle
On Error GoTo Err_Handler
Err.Number = 0
originalReferenceStyle = Application.ReferenceStyle
Application.ReferenceStyle = xlR1C1
' =INDEX({0},MATCH(1,({1}={2})*({3}={4}),{5}))
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)
IndexMatch1 = Application.Evaluate(myFormula)
Err_Handler:
If (Err.Number <> 0) Then MsgBox Err.Description
Application.ReferenceStyle = originalReferenceStyle
End Function