Help creating a formula to find a substring in a cell to a substring in another cell

Hi Formula experts… I need help setting up a formula that will return TRUE (or 1) if these conditions are met...
In 2 strings where elements (substrings) are separated by commas, see if any one element (substring) is present as an element (substring) in the other string.

Cell A1 = “A,B,C” -- Cell A2 = “Y,Z,B” = True (B element is in both strings)
Cell A1 = “A,B,C” -- Cell A2 = “Y,Z,Z” = False (element is in only one string)
Cell A1 = “A,B,C” -- Cell A2 = “A,B,C” = True (all elements in both strings)

Is it possible to do with a formula?

try this one:

Formula:

Please Login or Register  to view this content.

with CSE (Control+Shift+Enter)
or maybe someone else will give you better or shorter solution

Try this:

=SUMPRODUCT(COUNTIF(A1,"*"&TRIM(MID(SUBSTITUTE(","&B1,",",REPT(" ",200)),
ROW(INDIRECT("1:"&LEN(A1)-LEN(SUBSTITUTE(A1,",",""))+1))*200,200))&"*"))>0

Phuocam and Sandy666. Thanks very much - ingenious. They are so close to what I need. the only challenge with both suggestions is that...

A1: "A,B,C"
A2: "AA"

will return TRUE - where an exact match is needed so should return FALSE. Could either of these suggestions be modified for an exact match?
Thank you again, really appreciated.

Try again:

=COUNT(SEARCH(","&TRIM(MID(SUBSTITUTE(","&B1,",",REPT(" ",200)),ROW(INDIRECT("1:"&LEN(B1)-LEN(SUBSTITUTE(B1,",",""))+1))*200,200))&",",","&A1&","))>0

Ctrl+Shift+Enter.

Brilliant!, That's it! Thank you very much!

Phuocam - I thought we had it, but I've noticed that

A1 = aa,bb,cc
A2 = cc
returns FALSE... but...

A1 = cc,aa,bb
A2 = cc
returns TRUE (which is correct) - so seems that it is not picking up the last position for a match. Any help is very much appreciated.