How to find a longer string in an array of shorter ones in Excel-CodePudding

In Excel, I have arrays of strings, and I am trying to see if they "match" another array of shorter strings. Looking for a function that will return true\false.

Long Array:

A1 7-Zip 22.01 (x64)
A2 Adobe Acrobat
A3 Adobe Acrobat (64-bit)
A4 Adobe Acrobat DC
A5 Adobe Acrobat Reader

Short Array:

A1 7-Zip
A2 ABS PDF Install
A3 Adobe Acrobat Reader
A4 Adobe Genuine Service

For example:

"Long Array-A1" should match "Short Array-A1"
"Long Array-A5" should match "Short Array-A3"
"Long Array-A2" should not match "Short Array-A3"

What I have tried:

=MATCH(LOWER("*"&ShortStrings!A2&"*"),LOWER("*"LongStrings!A2:A10"*"),0)

Returns:

A1 7-Zip 22.01 (x64) - No Match - Should Match Short-A1
A2 Adobe Acrobat - No Match - Good
A3 Adobe Acrobat (64-bit) - No Match - Good
A4 Adobe Acrobat DC - No Match - Good
A5 Adobe Acrobat Reader - Match to A3

CodePudding user response：

Use:

=OR(ISNUMBER(MATCH("*"&ShortStrings!$A$1:$A$4&"*",LongStrings!A1,0)))

Or:

=OR(ISNUMBER(SEARCH(ShortStrings!$A$1:$A$4,LongStrings!A1)))

CodePudding user response：

Ended up tweaking one of the submissions slightly to get it to work.

Psuedo code
=OR(ISNUMBER(SEARCH("*"&ShortStrings!$A$1:$A$5&"*",LongStrings!A1)))

Real code
=OR(ISNUMBER(SEARCH("*"&Table15[Excluded Name]&"*",A2)))

Long Strings: Long Strings

Short Strings: Short Strings

Thank you for the help everyone !