Formula
=VLOOKUP("*" &A5&"*",Sheet2!B:B,1,0)
VBA you could use
Sub Oh_Yah()
Dim sh As Worksheet
Dim ws As Worksheet
Dim Shrng As Range, c As Range, shRw As Long
Dim Wsrng As Range, w As Range, wsRw As Long
Set sh = Worksheets("Sheet2")
Set ws = Worksheets("Sheet1")
With ws
wsRw = .Cells(Rows.Count, "A").End(xlUp).Row
Set Wsrng = .Range(.Cells(5, 1), .Cells(wsRw, 1))
End With
With sh
shRw = .Cells(Rows.Count, "B").End(xlUp).Row
Set Shrng = .Range(.Cells(1, 2), .Cells(shRw, 2)).SpecialCells(xlCellTypeConstants, 23)
End With
For Each c In Wsrng.Cells
Set w = Shrng.Find(what:=c, lookat:=xlPart)
If Not w Is Nothing Then
c.Offset(, 1) = w
Else: MsgBox "Not Found"
End If
Next c
End Sub
Bookmarks