Sub Rechercher()
Dim cible$, tablo, resu(), i&, s, j%, n&
cible = CStr([B2])
tablo = [Tableau1].Resize(, 2) 'matrice, plus rapide, au moins 2 éléments
ReDim resu(1 To UBound(tablo), 1 To 1)
For i = 1 To UBound(tablo)
s = Split(tablo(i, 1)) 'séparateur espace
For j = 0 To UBound(s)
If s(j) = cible Then
n = n + 1
resu(n, 1) = tablo(i, 1)
Exit For
End If
Next j, i
'---restitution---
With [A6] '1ère cellule de destination
If n Then .Resize(n) = resu
.Offset(n).Resize(Rows.Count - n - .Row + 1).ClearContents 'RAZ en dessous
End With
End Sub
Sub RAZ()
Range("B2,A6:A" & Rows.Count) = ""
End Sub