vba index match with multiple criteria

Question

I am trying to convert an R1C1 vba formula into an "A1" type of code.

ActiveCell.Formula2R1C1 = "=INDEX(C3,MATCH(RC[-1]&R2C6,C1&C5,0))"

in spreadsheet it is: =INDEX($C:$C,MATCH(H2&$F$2,$A:$A&$E:$E,0))

I tried the following but I am not getting the correct result, please guide.

    a = Application.WorksheetFunction.Index(Range("C:C"), _
    Application.WorksheetFunction.Match(Range("H2").Value, Range("A:A"), 0) + _
    Application.WorksheetFunction.Match(Range("F2").Value, Range("E:E"), 0), 0)

Thank you.

I think you're going to have to use Evaluate... $A:$A&$E:$E doesn't translate well to VBA. — BigBen
It is going to be quicker if you load the used ranges into arrays and loop the first dimension of the array. — Scott Craner

BigBen BigBen · Accepted Answer · 2020-07-02T16:16:31

It's not straightforward to convert a formula like =INDEX($C:$C,MATCH(H2&$F$2,$A:$A&$E:$E,0) to VBA, because of the $A:$A&$E:$E. You'd need to create an array that joins the values in column A and E. Additionally, Match when used with an array is slower than when used with a Range.

One workaround is just to use Evaluate here:

Dim a 
a = ActiveSheet.Evaluate("=INDEX($C:$C,MATCH(H2&$F$2,$A:$A&$E:$E,0))")

vba index match with multiple criteria

1 Answers