Assistance needed involving lookups from two columns

[kittensincups]

Hi there, excel gurus!

I have two columns. For simplicitlys sake, I have attached two columns of dummy data to display the various types of names in a simple manner.

In the below workbook, there are a list of names in column 2. I would like to lookup each name in column 2 and see if it exists in column 1. If it does it exist, I would like an output of "TRUE." If it does not exist, I would like an output of "FALSE."

In my below workbook, each name in column 2 should output "TRUE." Each name in column 2 MATCHES a name in column 1. (In my real-life data - This is the problem. Every name has a match, but some names contain the person's middle name in column 1 but not column 2, and vice versa.

For example:
Doe, John should match with Doe, John
Jones, Adam Michael should match with Jones Michael
Brown, Marcus Johnson should match with Brown, Marcus

In the real data, the data is scrambled; the names are not matching neatly side-by-side. I am thinking I need to utilize possibly a Lookup or Match formula, maybe with a wildcard?

Initially, I tried a simple IFERROR VLookup with a wildcard; but this formula was unable to match up "Brown, Marcus" and "Brown, Marcus Johnson"

Perhaps this is an easy problem and I'm just not building the correct Lookup formula to catch these variations in Last names, First Names, and Middle Names.

I appreciate any help you can offer!

Dummy Data.xlsx
	A	B	C
1	Column 1	Column 2	Does the name in Column 2 match a name in Column 1?
2	Doe, John	Doe, John
3	Jones, Adam Michael	Jones Michael
4	Brown, Marcus	Brown, Marcus Johnson
Sheet1

 

[Saba Sabaratnam]

You could use this formula in C2 to c4. But it will not match all names

=XLOOKUP(TRUE,ISNUMBER(SEARCH("*"&A2&"*",B2,1)),TRUE,FALSE)

See below.

Kind regards

Saba

 

[kittensincups]

@Saba Sabaratnam Thank you for this reply

I apologize, firstly. In my Sample Excel Data, "Jones Michael" was supposed to read "Jones, Michael" with a comma.

So, I do need "Jones, Michael" to return a TRUE for "Jones, Adam Michael."

Any ideas?

 

[Saba Sabaratnam]

You could use this approach that involves lot more formulas

Enter the following formulas in B2,C2 and D2 in order and copy it down

=MID(A2,1,FIND(",",A2,1)-1)

=IFERROR(IF(D2=TRIM(MID(SUBSTITUTE(SUBSTITUTE(A2,",","")," ",REPT(" ",1000)),500,1000)),"&",TRIM(MID(SUBSTITUTE(SUBSTITUTE(A2,",","")," ",REPT(" ",1000)),500,1000))),"")

=IFERROR(TRIM(RIGHT(SUBSTITUTE(SUBSTITUTE(A2,",","")," ",REPT(" ",1000)),400)),"")

Copy these formulas to F2,G2 and H2 and copy it down

Then enter the following formula in I2 and copy it down

=IF(SUM(COUNTIFS(B2:D2,F2:H2))=2,TRUE,FALSE)

Kind regards

Saba