Sub Macro1()
Dim OS As Worksheet 'déclare la variable OS (Onglet Source)
Dim OD As Worksheet 'déclare la variable OD (Onglet Destination)
Dim TS As Variant 'déclare la variable TS (Tableau Source)
Dim TD As Variant 'déclare la variable TD (Tableau Destination)
Dim I As Integer 'déclare la variable I (Incrément)
Dim J As Integer 'déclare la variable J (incrément)
Set OS = Worksheets("Feuil2") 'définit l'onglet OS
Set OD = Worksheets("Feuil1") 'définit l'onglet OD
TS = OS.Range("A1").CurrentRegion 'définit le tableau source
TD = OD.Range("A1").CurrentRegion 'définit le tableau source
For I = 2 To UBound(TD, 1)
For J = 2 To UBound(TS, 1)
If TD(I, 2) = TS(J, 1) Then
If InStr(1, TD(I, 1), TS(J, 3), vbTextCompare) <> 0 Then
OD.Cells(I, "C").Value = TS(J, 2)
Exit For
End If
End If
Next J
Next I
End Sub