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

I'm trying to use formulae only to create a new list of data from a master data set using criteria in a lookup column.
I'm hoping for my formula to follow these steps:
1 Take value in E3.
2 Lookup cells in A3:A14 partially containing value in E3.
3 When a match is found, stop, copy complete value in column A plus adjacent value in column B. Add value to H & I column (H3:I3 for first entry in example)
4 After first entry, formula will move to next row in column H (H4)
5 Move to next row in column A and continue lookup.
6 Repeat step 3 & 4 everytime a match is found.

This is pretty much the jist of it. I'm new to formulae in excel so any help would be appreciated.
vlookup is great but couldn't combine with other function to do step 4

Please find attached a copy of the sample excel file as shown in the original query post.
Hope this helps.

In J3

=IFERROR(INDEX(A$3:A$14,SMALL(IF(ISNUMBER(SEARCH($E$3,$A$3:$A$14)),ROW($A$3:$A$14)-ROW($A$3)+1,""),ROWS($A$3:A3))),"")


...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

Then copy across to K and down

Repeat for other cells, changing highlighted cell

Originally Posted by JohnTopley

In J3

=IFERROR(INDEX(A$3:A$14,SMALL(IF(ISNUMBER(SEARCH($E$3,$A$3:$A$14)),ROW($A$3:$A$14)-ROW($A$3)+1,""),ROWS($A$3:A3))),"")


...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

Then copy across to K and down

Repeat for other cells, changing highlighted cell

Many thanks.
Now what if I wanted to, rather than have three separate lists corresponding to each value on my lookup set, have only one list comprising of values in column A containing values in my lookup set.

Therefore the new list would contain all values in column A (below header) apart from A11 since it does not contain any values from lookup set.

I tried changing the $E$3 to an array ($E$3:$E$5) but the SEARCH formula does not accept an array.

Try

=IFERROR(INDEX($A$3:$A$14,SMALL(IF(--ISNUMBER(SEARCH({"LG","F3","LQ"},$A$3:$A$14)),ROW($A$3:$A$14)-ROW($A$3)+1,""),ROWS($A$3:$A3))),"")



...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

Originally Posted by JohnTopley

Try

=IFERROR(INDEX($A$3:$A$14,SMALL(IF(--ISNUMBER(SEARCH({"LG","F3","LQ"},$A$3:$A$14)),ROW($A$3:$A$14)-ROW($A$3)+1,""),ROWS($A$3:$A3))),"")



...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

Rather than inputting the exact values in the formula, is there no way of selecting a range of cells like E3:E5.

Currently the criteria list is only three values, but could extend to up to 15 values (Master data set would also increase) which could make inputting values individually time consuming.

Apologies for making the problem a little more complicated, but any help would be grateful.

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]

Originally Posted by JohnTopley

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]

Thank you John, however I'm trying to avoid using macro as this spreadsheet will be edited by several people and I'm not sure what their knowledge with VBA will be.
I think, excel formulae would be simpler to comprehend.

In your last excel formula below, I will try and use a helper column to create an array of strings from a range of cells. This would then be the input to your formula below

=IFERROR(INDEX($A$3:$A$14,SMALL(IF(--ISNUMBER(SEARCH({"LG","F3","LQ"},$A$3:$A$14)),ROW($A$3:$A$14)-ROW($A$3)+1,""),ROWS($A$3:$A3))),"")

Thank you.

I tried it with a text string but it did not work (or could not get it to work!).

And there no need for any knowledge of VBA,

After entering data, all anyone has to do is click "RUN" !!!!

Originally Posted by JohnTopley

I tried it with a text string but it did not work (or could not get it to work!).

And there no need for any knowledge of VBA,

After entering data, all anyone has to do is click "RUN" !!!!

The purpose it maximise the value of the spreadsheet.
If I limit the number of people that can improve it, then it defeats the purpose.

The list is as it seems now, however, the "lookup set" may expand to up to 20 values, or even something simple like moving "Master data set" to a different column may require a modification.
People generally aren't accustomed to tweeking VBA in excel (intimidating to some), and would rather deal with excel formulae instead.

I hope you can understand.

I will close this query as solved. I appreciate you help.

Have a lovely sunny day.

Thank you.