Comparing two sets of columns

[karkas]

I am trying to cross reference 4 columns of names.
Data arrangement:
In row C I have last name which usually (not always) ends in a comma or period (~2300 rows).
In row D I have the first name of the person from row C (same # of rows).

In row L I have a master list of last names (~13,000 rows)
In row M I have the first name of the person from row L (same # of rows).


What I am looking for:
I need to see if the people listed in rows C & D match the master list of people in rows L & M.

I want to Check the names in Column C against the mast list in column L, but there are two problems.
1) The names in column C usually end in a comma or period that I need to ignore.
2) Once it matches the name in C to L, it needs to check to see if the first name in column D matches the first name in M. (It needs to match on BOTH first and last name).

Not sure if that is confusing and/or vague. Please let me know if I need to clarify further.

Thanks in advance!

 

[jtakw]

Hi,

You didn't specify what you want if C & D is a match in L & M, so assuming you just want it Flagged Yes or No:

Book1
	C	D	E	F	L	M
1	Last	First	Match?		Last	First
2	Doe.	John	Yes		Doe	May
3	Doe,	Jack	Yes		Doe	Jane
4	Doe	Jill	No		Doe	Jan
5	Doe	Jane	Yes		Doe	John
6					Doe	Jack
Sheet535

Cell Formulas
Range	Formula
E2	=IF(ISNUMBER(LOOKUP(2,1/SEARCH(SUBSTITUTE(SUBSTITUTE(C2,".",""),",","")&"|"&D2,L$2:L$6&"|"&M$2:M$6))),"Yes","No")

Formula copied down.

 

[karkas]

Wow, that is amazing! It seems to work! I'll look more closely and let you know if anything seems off!

Thanks again! HUGE help!

 

[jtakw]

You're welcome, thanks for the feedback.

 

[karkas]

I lost my script because of a crash and can no longer read this. It just looks like code now. Is there a way to reveal the info that jtakw posted?

Thanks for anyone that can help.

 

[Fluff]

From what I can make out it was
=IF(ISNUMBER(LOOKUP(2,1/SEARCH(SUBSTITUTE(SUBSTITUTE(C2,".",""),",","")&"|"&D2,L$2:L$6&"|"&M$2:M$6))),"Yes","No")