Partial String Match Based on Range and Return Matched Word

Question

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

=IF(OR(COUNTIF(B2,"*"&[@Column1]&"*")),""&[@Column1]&"","")

hansvogelaar · Answer

mhasanali381&nbsp;
In G2 as an array formula confirmed with Ctrl+Shift+Enter:
&nbsp;
=IF([@Desc]="","",TEXTJOIN(", ",TRUE,IF(ISNUMBER(SEARCH(" "&amp;TableName[[Column1]:[Column2]]&amp;" "," "&amp;[@Desc]&amp;" ")),TableName[[Column1]:[Column2]],"")))
&nbsp;
where TableName is the name of the table.

mhasanali381 · Answer

Thanks&nbsp;@HansVogelaar,&nbsp;Your formula is working. However, it is returning the same string Many Times. Check attached Screenshot&nbsp;Is there a way to keep only unique words?&nbsp;&nbsp;&nbsp;&nbsp;

hansvogelaar · Answer

mhasanali381&nbsp;
Are you using Excel in Microsoft 365?

mhasanali381 · Answer

HansVogelaar&nbsp;No, I'm using office 2019&nbsp;&nbsp;

hansvogelaar · Answer

mhasanali381&nbsp;
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 " " &amp; s &amp; " " Like "* " &amp; cel.Value &amp; " *" Then
            dct(cel.Value) = 1
        End If
    Next cel
    GetMatches = Join(dct.Keys, ", ")
End Function
And the formula:
=GetMatches([@Desc],TableName[[Column1]:[Column2]])

Forum Discussion

Partial String Match Based on Range and Return Matched Word

5 Replies

Resources