=ИНДЕКС([таблица2.xlsx]Sheet1!$D:$D;ПОИСКПОЗ(A2;[таблица2.xlsx]Sheet1!$A:$A;0))

Sub dsd()
Dim sh As Worksheet, sh2 As Worksheet
Dim cell As Range
Dim lr As Long, i As Long
Set sh = Workbooks("Таблица1").Worksheets("Sheet1")
Set sh2 = Workbooks("Таблица2").Worksheets("Sheet1")
lr = sh.Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To lr
Set cell = sh2.Columns(1).Find(sh.Cells(i, 1))
    If Not cell Is Nothing Then
        sh.Cells(i, 4) = cell.Offset(0, 3)
    Else
        sh.Cells(i, 4).Interior.ColorIndex = 3
    End If
Next i
End Sub