excel vba - multiple criteria index match

Question

I'm trying to do a multiple criteria index match function in vba but I can't seem to get the results. The code I used is the following:

  wsDest.Range(wsDest.Cells(i, X), wsDest.Cells(i, X)) = _
  Application.WorksheetFunction.Index(wsSour.Range("C3:C8763"), _
    Application.WorksheetFunction.Match(wsDest.Cells(i, 1) & "&" & wsDest.Cells(i, 2), _
      wsSour.Range("A3:A8763") & "&" & wsSour.Range("B3:B8763"), 0), 0)

For the match portion I was trying to use excel's method of

=MATCH(criteria1 & criteria2,range1 & range2,0)

Answer 1

You can use the WorkSheet.Evaluate method to do this.

Here's a simple example:

Sub Tester()

    Dim v, sht, a1, a2

    Set sht = ActiveSheet

    a1 = sht.Cells(7, 1).Address(False, False)
    a2 = sht.Cells(7, 2).Address(False, False)

    v = sht.Evaluate("MATCH(" & a1 & "&" & a2 & ",A2:A5&B2:B5,0)")

    sht.Range("B9") = v

End Sub

EDIT: here's a more robust example taking into account the different sheets

Sub Tester2()

    Dim v, shtDest, shtSrc, a1, a2, i

    Set shtDest = ThisWorkbook.Sheets("Dest")
    Set shtSrc = ThisWorkbook.Sheets("Source")

    i = 1

    a1 = "'" & shtDest.Name & "'!" & shtDest.Cells(i, 1).Address(False, False)
    a2 = "'" & shtDest.Name & "'!" & shtDest.Cells(i, 2).Address(False, False)

    Debug.Print a1, a2

    v = shtSrc.Evaluate("MATCH(" & a1 & "&" & a2 & ",A2:A9&B2:B9,0)")

    If Not IsError(v) Then
        shtDest.Cells(i, 3).Value = shtSrc.Range("C2:C9").Cells(v).Value
    End If

End Sub