Q:

VBA 2 criteria index match lookup using evaluate, Excel

Say I am trying to get a lookup with 2 criteria in the example picture below: 
example picture
Can anyone tell me what is wrong with this code?  I am trying to use a index/match lookup with 2 criteria.  
Sub test()
Dim imTest As Variant
Dim school As String
Dim place As String
Dim Criteria1 As String
Dim Criteria2 As String

Dim ws As Worksheet
Set ws = ThisWorkbook. Worksheets("Sheet1")

school = ws. Cells(2, 8)
place = ws. Cells(2, 9)
Criteria1 = school
Criteria2 = place

'error here
imTest = Evaluate("Index($c$2:$c$5, Match( criteria1  & criteria2 , $A$2:$A$5&$B$2:$B$5, 0))")
If IsError(imTest) Then
ws. Cells(2, 10) = "Err"
Else
ws. Cells(2, 10) = imTest
End If

End Sub

But however, this code works, by editing the evaluate line like so: 
Sub test()
Dim imTest As Variant
Dim school As String
Dim place As String
Dim Criteria1 As String
Dim Criteria2 As String

Dim ws As Worksheet
Set ws = ThisWorkbook. Worksheets("Sheet1")

school = ws. Cells(2, 8)
place = ws. Cells(2, 9)
Criteria1 = school
Criteria2 = place

'this changed and no more error
imTest = Evaluate("Index($c$2:$c$5, Match( ""John""  & ""NJ"" , $A$2:$A$5&$B$2:$B$5, 0))")
If IsError(imTest) Then
ws. Cells(2, 10) = "Err"
Else
ws. Cells(2, 10) = imTest
End If

End Sub

What do I need to do to make the match argument work for variables?  I hope I'm not too confusing.  Thank you so much in advance for any help!  Thanks.  

A:

This should work.   Note there is a difference between using Application. Evaluate and Worksheet. Evaluate: the first will evaluate the formula in the context of whichever sheet happens to be active.   If that's not what you'd want then you should use the WorkSheet version. 
Sub test()

    Dim imTest As Variant
    Dim school As String
    Dim place As String
    Dim Criteria1 As String
    Dim Criteria2 As String

    Dim ws As Worksheet
    Set ws = ThisWorkbook. Worksheets("Sheet1")

    school = ws. Cells(2, 8)
    place = ws. Cells(2, 9)
    Criteria1 = school
    Criteria2 = place

    'note ws. Evaluate or you will get bad results when 
    '   Sheet1 is not the active sheet
    imTest = ws. Evaluate("Index($c$2:$c$5, Match(""" &  criteria1 & """&""" & _
                          criteria2 & """, $A$2:$A$5&$B$2:$B$5, 0))")

    If IsError(imTest) Then
        ws. Cells(2, 10) = "Err"
    Else
        ws. Cells(2, 10) = imTest
    End If

End Sub

