Find common elements between 2 delimited cells.

[mrthos]

Thanks in advance for any assistance. I'm looking to list the common elements between 2 delimited cells. Example below in Cells C1 thru C5 return matches between cells A1-A5 and B1-B5. There may be one or more or no exact matches. The cells may contain text or numbers but never a mix. Not sure it that would be a factor.

thank you!

 

[Fluff]

What version of Excel are you using?
I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[mrthos]

Thank you. Profile is updated...

 

[Fluff]

Thanks for that, how about

+Fluff 1.xlsm
	A	B	C
1
2	Dog, Cat	Sheep, Horse, Dog, Duck	Dog
3	Cat, Dog	Sheep, Horse, Dog, Duck, Goat, Cat	Cat, Dog
4	10,12	10,11,15,18	10
5	10,12	9,10,11,12,18,22	10, 12
6	9	8,10,11,12
7
Main

Cell Formulas
Range	Formula
C2:C6	C2	=LET(a,FILTERXML("<k><m>"&SUBSTITUTE(A2,",","</m><m>")&"</m></k>","//m"),TEXTJOIN(", ",,FILTER(a,ISNUMBER(SEARCH(a,B2)),"")))

 

[mrthos]

That seemed to work like a charm! Thank you very much!

 

[Fluff]

You're welcome & thanks for the feedback.