Formula that can compare cells and return results for partial matches

[Monty85]

Can anyone point in the right direction for a formula that can compare two cells and return a result for a partial match?

For example, I need to compare a list of names where one column may or may not contain a middle name. I.e;

A1 = John Smith
B1 = John F. Smith

I need a way to reference A1 and return a "True" result if the content of A1 is found somewhere in B1.

Appreciate any and all ideas.

 

[Georgiboy]

What version of Excel do you use?

 

[Legacy 143009]

If you are not using 365:

=COUNT(SEARCH(TRANSPOSE(FILTERXML("<t><s>"&SUBSTITUTE(A1," ","</s><s>")&"</s></t>","//s")),B1))=COUNTA(TRANSPOSE(FILTERXML("<t><s>"&SUBSTITUTE(A1," ","</s><s>")&"</s></t>","//s")))

This is an array formula. You must press Ctrl+Shift+Enter together after paste.

 

[Monty85]

What version of Excel do you use?

Office 365 version

 

[Monty85]

If you are not using 365:

=COUNT(SEARCH(TRANSPOSE(FILTERXML("<t><s>"&SUBSTITUTE(A1," ","</s><s>")&"</s></t>","//s")),B1))=COUNTA(TRANSPOSE(FILTERXML("<t><s>"&SUBSTITUTE(A1," ","</s><s>")&"</s></t>","//s")))

This is an array formula. You must press Ctrl+Shift+Enter together after paste.

Cool will give it a go - is there another option if im using Office 365

 

[Legacy 143009]

Cool will give it a go - is there another option if im using Office 365

Yeah here is a way shorter hand with 365. I can not test it but this may work also. Maybe even shorter is possible but I am not experienced in 365:

=LET(s,TEXTSPLIT(A1," "),COUNT(SEARCH(s,B1))=COUNTA(s))

 

[Georgiboy]

If it is just comparing names and ignoring middle names then maybe:

=LET(s,TEXTSPLIT(B1," "),TAKE(s,,1) & " " & DROP(s,,COLUMNS(s)-1) = A1)

This method will however return false for the below example:
John Smith | John Smithd

 

[Legacy 143009]

If it is just comparing names and ignoring middle names then maybe:

=LET(s,TEXTSPLIT(B1," "),TAKE(s,,1) & " " & DROP(s,,COLUMNS(s)-1) = A1)

This method will however return false for the below example:
John Smith | John Smithd

Oh yes, my suggestion will also return John F. as TRUE likewise John Smithd. Maybe you should go with Georgiboy's suggestion.

 

[JvdV]

Book1
	A	B	C
1	John Smith	John F. Smith	TRUE
2	John Smith	John Smith	TRUE
3	John Smith	John Smiths	FALSE
Sheet1

Cell Formulas
Range	Formula
C1:C3	C1	=OR(A1=B1,COUNTIF(B1,SUBSTITUTE(A1," "," * ")))

 

[Monty85]

If it is just comparing names and ignoring middle names then maybe:

=LET(s,TEXTSPLIT(B1," "),TAKE(s,,1) & " " & DROP(s,,COLUMNS(s)-1) = A1)

This method will however return false for the below example:
John Smith | John Smithd

This is awesome, thats actually more useful as I'd prefer that situation to return False so it could be checked manually.

Is this a Office 365 only formula? Or would that work in either version?