Vlookup - create new list from existing list based on partial match

VBA offering:

Sub Get_List()

Dim loookup() As String
Dim InArr() As Variant
Dim OutArr() As Variant
Dim List() As String
Dim Destination As Range

lr1 = Cells(Rows.Count, "A").End(xlUp).Row
InArr = Range("A3:B" & lr1)
lr2 = Cells(Rows.Count, "E").End(xlUp).Row
Lookup = Range("E3:E" & lr2)
ReDim OutArr(1 To 2, 1 To 1)


k = 0
For i = 1 To UBound(InArr, 1)
    For j = 1 To UBound(Lookup, 1)
        If InStr(1, InArr(i, 1), Lookup(j, 1)) Then
           k = k + 1
           ReDim Preserve OutArr(1 To 2, 1 To k)
           OutArr(1, k) = InArr(i, 1)
           OutArr(2, k) = InArr(i, 2)
           Exit For
        End If
    Next j
Next i
'
' write out results
'


Set Destination = Range("H3")
Destination.Resize(UBound(OutArr, 2), UBound(OutArr, 1)).Value = Application.Transpose(OutArr)

End Sub

Copy the Excel VBA code
Select the workbook in which you want to store the Excel VBA code
Press Alt+F11 to open the Visual Basic Editor
Choose Insert > Module
Edit > Paste the macro into the module that appeared
Close the VBEditor
Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)
[B][I]