Partial string match and lookup in Excel-VBA

I'm pretty lost with something I have to do with Excel.

I need to make a recursive lookup between two tables to find a match, and insert a specific value in one cell.

In the next example, I show you what should be done, I hope anyone could help! :)

Basically, I need to look for values from column "E" within each "B" cell, and if there is any match, insert the specific "F" value in "C".

Example

Error after increasing range of Trigger column

3rd pic

I don't know if a formula is enough, but maybe a macro is needed.

I would appreciate any help.

Thanks in advance!

Try this UDF using dictionary. If no match found it will return blank. Instead of parsing the Text column cell it loops through Trigger column's dictionary keys to find match in the Text columns cell value.

dict.CompareMode is set to be vbTextCompare for non-case senseitive comparison to find matches. For case sensitive comparison we can set this to vbBinaryCompare

excelmacromastery Page

Option Explicit

Public Function PartialStrMatch(str As String, matchCol As Range, lookupCol As Range) As String
Dim dict As Object, i As Long, cl As Range
Set dict = CreateObject("Scripting.Dictionary")
dict.CompareMode = vbTextCompare

For Each cl In matchCol
    If Not dict.exists(cl.Value) Then
    dict.Add cl.Value, lookupCol(Application.Match(cl, matchCol, 0)).Value
    End If
Next cl

For i = 0 To dict.Count - 1
    If InStr(1, str, dict.Keys()(i), vbTextCompare) > 0 Then
        PartialStrMatch = dict.Items()(i)
        Exit For
    End If
Next i

End Function