Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Ligne As Long, Source As Range
Set Source = [tableau1]
If Not Intersect(Range("B5"), Target) Is Nothing And Target.Count = 1 Then
If Target = "" Then
Target.Offset(, 1) = ""
Else
Ligne = Application.IfError(Application.Match(Target, Source.Columns(1), 0), 0)
Target.Offset(, 1) = Source(Ligne, 2)
End If
End If
End Sub