Formulas to see if names in one column exists on the names in the second column

Hey everyone,

I am at work and I need some help!
I have two columns listing about 400 and 500 names respectively.
First Column has names in alphabetical order and the second column is random.
One column has names in this format: Last name, First name
and the other column lists the names as first name and then last name without the comma
I am trying to see if the list of names in one column exists in the other.

Is there a way to see this without changing the format of the names in each column? If so do I have to erase the comma?

Thanks in advance !

Name Lists (1).xlsx

You get better help on your question, if you add an small excel file, without confidential information on this forum.

Please also add the desired (expected) result in the file.

Untested, maybe something like this?

=iferror(vlookup(substitute(B1,",",""),A1:A500,1,0),"Nope!")

Originally Posted by ranman256

in LAST,FIRST field (here called col A),
have one col (ex. Y) get the last name of A=left(a1,instr(a1,",")-1)
then (ex Z) get first name, have the formula: = mid(a1,instr(a1,",")+1)

in another check the the 2 first name colunns, and the two last names

Isn't INSTR() a VBA only function? The formula equivelant would be a MID/FIND combination.

One way...

Data Range

	A	B	C	D	E
1	Name	Is A Match	------	------	Name
2	Bear, Yogi	TRUE			Sam Snead
3	Del Grecco, Al	TRUE			Yogi Bear
4	West, Roger	FALSE			Frank Zappa
5	Clearwater, Burt	FALSE			Al Del Grecco

This formula entered in B2 and copied down:

=ISNUMBER(MATCH(MID(A2&" "&A2,FIND(",",A2)+2,LEN(A2)-1),E:E,0))

Post some representative sample data!

There appears to be an error with the attachment, at least for me.

Regards

Originally Posted by XOR LX

There appears to be an error with the attachment, at least for me.

Same here.

Excel (2010) says it can't open the file.

Try this one in B1 and copy down

=IFERROR(VLOOKUP(REPLACE(A1,1,FIND(" ",A1),"")&" "&LEFT(A1,FIND(",",A1)-1),$C$1:$C$412,1,0),"N/A")

I was able to download the file from post #1 and open it in Excel 2007 & 2013.

Please see attached file.

Doesn't work on 2 word last names.

Row 56: Del Valle, Clark

Added Clark Del Valle to column C and the formula returned N/A.