Partial String Match Based on Range and Return Matched Word

%3CLINGO-SUB%20id%3D%22lingo-sub-2414324%22%20slang%3D%22en-US%22%3EPartial%20String%20Match%20Based%20on%20Range%20and%20Return%20Matched%20Word%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2414324%22%20slang%3D%22en-US%22%3E%3CP%3EI'm%20Trying%20to%20Extract%20the%20Name%20from%20the%20Description%20if%20it%20matches%20our%20Customer%20Names%20List.%20Please%20see%20attached%20image%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere%20is%20What%20I%20want%20to%20do.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20Column%20B%20contains%20any%20word(Partial%20String%20Match)%20that%20matches%20with%20words%20in%20Column%20I%20and%20J%20then%20return%20the%20matched%20word%20in%20Columns%20G%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECurrently%2C%20My%20Formula%20is%20only%20finding%20the%20word%20on%20the%20same%20row%2C%20not%20the%20whole%20Columns%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DIF(OR(COUNTIF(B2%2C%22*%22%26amp%3B%5B%40Column1%5D%26amp%3B%22*%22))%2C%22%22%26amp%3B%5B%40Column1%5D%26amp%3B%22%22%2C%22%22)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-left%22%20image-alt%3D%22CopyExcel.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F286133i09527805A6342A3C%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22CopyExcel.png%22%20alt%3D%22CopyExcel.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2414324%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2414486%22%20slang%3D%22en-US%22%3ERe%3A%20Partial%20String%20Match%20Based%20on%20Range%20and%20Return%20Matched%20Word%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2414486%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1070685%22%20target%3D%22_blank%22%3E%40mhasanali381%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20G2%20as%20an%20array%20formula%20confirmed%20with%20Ctrl%2BShift%2BEnter%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DIF(%5B%40Desc%5D%3D%22%22%2C%22%22%2CTEXTJOIN(%22%2C%20%22%2CTRUE%2CIF(ISNUMBER(SEARCH(%22%20%22%26amp%3BTableName%5B%5BColumn1%5D%3A%5BColumn2%5D%5D%26amp%3B%22%20%22%2C%22%20%22%26amp%3B%5B%40Desc%5D%26amp%3B%22%20%22))%2CTableName%5B%5BColumn1%5D%3A%5BColumn2%5D%5D%2C%22%22)))%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3Ewhere%20TableName%20is%20the%20name%20of%20the%20table.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2417537%22%20slang%3D%22en-US%22%3ERe%3A%20Partial%20String%20Match%20Based%20on%20Range%20and%20Return%20Matched%20Word%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2417537%22%20slang%3D%22en-US%22%3E%3CP%3EThanks%26nbsp%3B%40HansVogelaar%2C%26nbsp%3B%3C%2FP%3E%3CP%3EYour%20formula%20is%20working.%20However%2C%20it%20is%20returning%20the%20same%20string%20Many%20Times.%20Check%20attached%20Screenshot%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20there%20a%20way%20to%20keep%20only%20unique%20words%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22mhasanali381_0-1622834018344.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F286439iFA28BE3F56197107%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22mhasanali381_0-1622834018344.png%22%20alt%3D%22mhasanali381_0-1622834018344.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
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

 

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

 

CopyExcel.png

 

5 Replies

@mhasanali381 

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?

 

mhasanali381_0-1622834018344.png

 

 

 

@mhasanali381 

Are you using Excel in Microsoft 365?

@Hans Vogelaar No, I'm using office 2019

 

mhasanali381_0-1622834226268.png

 

@mhasanali381 

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:

=GetMatches([@Desc],TableName[[Column1]:[Column2]])