Sub correspondance()
With Sheets("Feuil1")
For Each c In .Cells(2, 1).Resize(Application.CountA(.[A:A]) - 1, 1)
ligne = Application.Match(c, .[D4:D7], 0) 'plage [D4:D7] à adapter
If IsError(ligne) Then
c.Offset(0, 1) = "Pas de correspondance"
Else
c.Offset(0, 1) = .Cells(ligne + 3, 5)
End If
Next c
End With
End Sub