Index Match multiple criteria

[tiredofit]

I am trying to create an index match formula based on multiple criteria, as per this article:

https://exceljet.net/formula/index-and-match-with-multiple-criteria

In particular, I would like to use the NON-ARRAY formula, as stated in the article, ie:

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]=INDEX(rng1,MATCH(1,INDEX((A1=rng2)*(B1=rng3)*(C1=rng4),0,1),0))
[/FONT]

This is my code but it errors on the last line:

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]    Dim rng1 As Range
    
    With Sheet1
    
        Set rng1 = .Range(.Cells(5, 5), .Cells(11, 5))[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]
    End With[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]
    Dim rng2 As Range
    
    With Sheet1
    
        Set rng2 = .Range(.Cells(5, 2), .Cells(11, 2))[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]
    End With
    
    Dim rng3 As Range
    
    With Sheet1
    
        Set rng3 = .Range(.Cells(5, 3), .Cells(11, 3))

[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]    End With
    
    Dim rng4 As Range
    
    With Sheet1
    
        Set rng4 = .Range(.Cells(5, 4), .Cells(11, 4))[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]    
    End With
 
    Dim abc
    
    abc = Application.Index(rng1, Application.Match(1, Application.Index(("T-shirt" = rng2) * ("Large" = rng3) * ("Red" = rng4), 0, 1), 0))

Can someone please tell me what is wrong?

Thanks


[/FONT]

 

[DanteAmor]

Try this way

Sub test()
  Dim rng1 As Range, rng2 As Range, rng3 As Range, rng4 As Range, abc As Variant
  Set rng1 = Range(Cells(5, 5), Cells(11, 5))
  Set rng2 = Range(Cells(5, 2), Cells(11, 2))
  Set rng3 = Range(Cells(5, 3), Cells(11, 3))
  Set rng4 = Range(Cells(5, 4), Cells(11, 4))
  abc = Evaluate("Index(" & rng1.Address & ",Match(1,(""T-shirt""=" & rng2.Address & ")*" & _
    "(""Large""=" & rng3.Address & ")*(""Red""=" & rng4.Address & "),0))")
End Sub