Convert index match with multiple criteria into VBA

hrh_dash 

Try this version:

Sub vlkupfromworkingfile()
    Dim ws As Worksheet
    Dim wsLastrow As Long
    Dim r As Long
    Const sSheet As String = "'C:\Users\hrhquek\Desktop\[working file.xlsx]Sheet1'!"

    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Set ws = Sheet1
    ws.Range("D:D").EntireColumn.Insert
    ws.Range("D1").Value = "Remarks"
    wsLastrow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    'On Error Resume Next
    For r = 2 To wsLastrow
        ws.Range("D" & r).Value = Evaluate("= Index(" & sSheet & "$D$2:$D$5000,MATCH(1,(" & _
            sSheet & "$C$2:$C$5000=C" & r & ")*(" & sSheet & "$P$2:$P$5000=P" & r & "),0))")
    Next r
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
End Sub