Partial String Match Based on Range and Return Matched Word

New Contributor

I'm Trying to Extract the Name from the Description if it matches our Customer Names List. Please see attached image 


Here is What I want to do.


If Column B contains any word(Partial String Match) that matches with words in Column I and J then return the matched word in Columns G


Currently, My Formula is only finding the word on the same row, not the whole Columns






5 Replies


In G2 as an array formula confirmed with Ctrl+Shift+Enter:


=IF([@Desc]="","",TEXTJOIN(", ",TRUE,IF(ISNUMBER(SEARCH(" "&TableName[[Column1]:[Column2]]&" "," "&[@Desc]&" ")),TableName[[Column1]:[Column2]],"")))


where TableName is the name of the table.

Thanks @HansVogelaar, 

Your formula is working. However, it is returning the same string Many Times. Check attached Screenshot


Is there a way to keep only unique words?







Are you using Excel in Microsoft 365?

@Hans Vogelaar No, I'm using office 2019





Thanks. so we can't use the new UNIQUE function.

A traditional formula is probably possible, but it'd be complicated. I'd prefer to use a custom VBA function - see the attached sample workbook. You'll have to allow macros when you open it.

This is the code:

Function GetMatches(s As String, rng As Range) As String
    Dim dct As Object
    Dim cel As Range
    If s = "" Then Exit Function
    Set dct = CreateObject(Class:="Scripting.Dictionary")
    For Each cel In rng
        If " " & s & " " Like "* " & cel.Value & " *" Then
            dct(cel.Value) = 1
        End If
    Next cel
    GetMatches = Join(dct.Keys, ", ")
End Function

And the formula: