Compare two strings and look for matching text

[bomfunk]

I would like to compare two columns to find matching text, and if there is, return "Match" on third column.

For example:

In A1:
XY12345 Cats and Dogs

In B1:
Batman with the Flash 12345

Since they both contain "12345", I will get "Match" in C1


Please tell me how to accomplish this with a formula. Thank you

 

[sericom]

That could be tricky unless the words have to be separated with a space.
eg 'an' and 'a' are in both those examples

 

[steve the fish]

Hi. I think im right in saying that the simple answer is you cant, at least you cant without more rules. They both contain an 'a' aswell. Does that make them a match? Do you understand what I mean? And even with more rules it would probably require VBA.

 

[bomfunk]

A match should be any 3 letters or numbers

 

[bomfunk]

3 or more

 

[FormR]

3 or more

Hi, here is one possible option you could try:

Excel 2013
	A	B	C
1	XY12345 Cats and Dogs	Batman with the Flash 12345	Match
2	XY12 Cats and Dogs	Batman with the Flash 12345	Not Match
3	XY12 Cats and Dogs	Batman wiXY12 Flash 123	Match
4	not a match	ot a ma	Not Match
5	is a match	bla atc bla	Match
6	ham egg and chips	hamburger and fries	Match
Sheet1

Cell Formulas
Range	Formula
C1	=IF(SUMPRODUCT(--(ISNUMBER(SEARCH(MID(SUBSTITUTE(A1," ","|"),ROW(INDEX(A:A,1):INDEX(A:A,LEN(A1)-2)),3),B1))))>0,"Match","Not Match")