CODEWITHSUNDEEP
excelvbaexcel-formulacustom-functions-excel
CrisoNikon
CrisoNikon

Reputation: 17

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!

Upvotes: 1

Views: 1495

Answers (2)

P.b
P.b

Reputation: 11415

=IFERROR(INDEX($F$1:$F$3,MATCH(1,--(COUNTIF($B1,"*"&$E$1:$E$3&"*")>0),0)),"No matching values") Wrapping the trigger range in "*" makes it accept all characters before or after the search string

Upvotes: 0

Naresh
Naresh

Reputation: 3034

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

enter image description here

Upvotes: 1

Related Questions