How to Index Match from multiple closed workbooks in excel.

Wadoodoo 

Try this:

Right-click the sheet tab.

Select View Code from the context menu.

Copy the following code into the worksheet module:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range
    Dim r As Long
    Dim s As String
    Dim f As String
    On Error GoTo ErrHandler
    If Not Intersect(Range("A1:C50"), Target) Is Nothing Then
        Application.ScreenUpdating = False
        Application.EnableEvents = False
        For Each rng In Intersect(Range("A1:C50"), Target).Rows
            r = rng.Row
            s = "'" & Range("A" & r).Value & "[" & Range("B" & r).Value & "]" & Range("C" & r).Value & "'!"
            f = "=INDEX(" & s & "$C:$C,MATCH(G4," & s & "$B:$B,0))"
            Range("E" & r).Formula = f
        Next rng
    End If
ExitHandler:
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    Exit Sub
ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
End Sub

Switch back to Excel.

Save the workbook as a macro-enabled workbook (.xlsm).

Make sure that you allow macros when you open the workbook.