Maybe:

Sub Habs21()
Range("B2:B" & Range("B" & Rows.count).End(3).Row).Formula = "=VLOOKUP(""*""&A2&""*"",Sheet2!B:B,1,0)"
End Sub