Partial Text match using VBA

[TeckTeck]

Hi i need help with this as i am new to VBA and dont have the knowledge if this can be done.

Column A is the master list, i would need to match Column E to Column A.
As long as all of Column E is found in Column A, then return the corresponding value in Column F to Column C with respect to the row that the text was match with.

Please help if possible or if not possible please let me know too

 

[bebo021999]

Book1
	A	B	C	D	E	F
1
2	The Fox jump Over	3			To-88-to	1
3	Dancing is so cool	4			How to do	2
4	I am not sure how to do this	2			Fox Jump	3
5	Please Help me 99				is so	4
6	11-To-88-To-32	1
Sheet1

Cell Formulas
Range	Formula
B2:B6	B2	=IFERROR(IFERROR(LOOKUP(2,1/(ISNUMBER(SEARCH(" "&$E$2:$E$5&" "," "&A2&" "))),$F$2:$F$5),LOOKUP(2,1/(ISNUMBER(SEARCH($E$2:$E$5,A2))),$F$2:$F$5)),"")

 

[bebo021999]

And, here is VBA solution

Option Explicit
Sub test()
Dim lr&, i&, j&, rng, res, st1 As String, st2 As String
lr = Cells(Rows.Count, "A").End(xlUp).Row
res = Range("A2:B" & lr).Value
lr = Cells(Rows.Count, "E").End(xlUp).Row
rng = Range("E2:F" & lr).Value
For i = 1 To UBound(res)
    st1 = " " & UCase(res(i, 1)) & " "
    For j = 1 To UBound(rng)
        st2 = " " & UCase(rng(j, 1)) & " "
        If InStr(1, st1, st2) Or InStr(1, st1, "-" & UCase(rng(j, 1)) & "-") Then
            res(i, 2) = rng(j, 2)
            Exit For
        End If
    Next
Next
Range("A2:B10000").ClearContents
Range("A2").Resize(UBound(res), 2).Value = res
End Sub

 

[TeckTeck]

Book1
	A	B	C	D	E	F
1
2	The Fox jump Over	3			To-88-to	1
3	Dancing is so cool	4			How to do	2
4	I am not sure how to do this	2			Fox Jump	3
5	Please Help me 99				is so	4
6	11-To-88-To-32	1
Sheet1

Cell Formulas
Range	Formula
B2:B6	B2	=IFERROR(IFERROR(LOOKUP(2,1/(ISNUMBER(SEARCH(" "&$E$2:$E$5&" "," "&A2&" "))),$F$2:$F$5),LOOKUP(2,1/(ISNUMBER(SEARCH($E$2:$E$5,A2))),$F$2:$F$5)),"")

Hi does this work if the master list and reference list are of 2 different worksheet?

 

[bebo021999]

Hi does this work if the master list and reference list are of 2 different worksheet?

Put the sheet's name before the range:
Like this:
Sheet1!$E$2:$E$5

 

[Peter_SSs]

You could also try this shorter version.
It assumes that at most only one of the values from Sheet2 column A would be found in the Sheet1 column A value.

TeckTeck.xlsm
	A	B
1
2	To-88-to	1
3	How to do	2
4	Fox Jump	3
5	is so	4
Sheet2

TeckTeck.xlsm
	A	B
1
2	The Fox jump Over	3
3	Dancing is so cool	4
4	I am not sure how to do this	2
5	Please Help me 99
6	11-To-88-To-32	1
Sheet1

Cell Formulas
Range	Formula
B2:B6	B2	=FILTER(Sheet2!B$2:B$5,ISNUMBER(SEARCH(Sheet2!A$2:A$5,A2)),"")

 

[TeckTeck]

Put the sheet's name before the range:
Like this:
Sheet1!$E$2:$E$5

Thank you soo much

 

[Peter_SSs]

Did you try the shorter option too?

 

[TeckTeck]

Did you try the shorter option too?

Hii yess i have tried it and it works well too Thank you for your solution too great community here!

 

[Peter_SSs]

Cheers. Thanks for the confirmation.

Also, if you are interested, you can achieve all the results with a single formula in the top cell only without the need to copy it down the rows.

TeckTeck.xlsm
	A	B
1
2	The Fox jump Over	3
3	Dancing is so cool	4
4	I am not sure how to do this	2
5	Please Help me 99
6	11-To-88-To-32	1
Sheet1

Cell Formulas
Range	Formula
B2:B6	B2	=BYROW(A2:A6,LAMBDA(rw,FILTER(Sheet2!B$2:B$5,ISNUMBER(SEARCH(Sheet2!A$2:A$5,rw)),"")))
Dynamic array formulas.