Hi,
If the name format is John Smith in Worksheet 1 and Smith,John in Worksheet
2 (i.e., no space after the comma), try the following formula (a modification
of the one in my previous reply)

=IF(SUMPRODUCT(--(A2=RIGHT(Sheet2!$A$2:$A$7,LEN(Sheet2!$A$2:$A$7)-FIND(",",Sheet2!$A$2:$A$7))&"
"&LEFT(Sheet2!$A$2:$A$7,FIND(",",Sheet2!$A$2:$ A$7)-1))),"Y","")

It, I believe, would work.

Regards,
B. R. Ramachandran

"Krista" wrote:

The name format:

John Smith worksheet 1 & Smith,John in worksheet 2

"B. R.Ramachandran" wrote:

Hi,

Try the following formula in B2 of Worksheet 1 (and then fill down the
formula in column B)

=IF(SUMPRODUCT(--(A2=RIGHT(Sheet2!$A$2:$A$7,LEN(Sheet2!$A$2:$A$7)-FIND(",",Sheet2!$A$2:$A$7)-1)&",
"&LEFT(Sheet2!$A$2:$A$7,FIND(",",Sheet2!$A$2:$ A$7)-1))),"Y","")

This formula assumes that names are in column A in each worksheet starting
at Row 2. It is vital that the first and last names (or vice versa) in all
entries are in the same format (separated by a comma followed by a space).
Furthermore, change the '7's (there are five of them) in the formula as the
last row number of Sheet 2.

I tested the formula with five names in sheet 1 (A2 to A6) and six names in
sheet (A2 to A7), and it worked.

Regards,
B. R. Ramachandran

"Krista" wrote:

I have two worksheets with name fields, however the name fields are reversed
on each worksheet.

Worksheet 1 list - first name, last name
worksheet 2 list - last name, first name

I need to compare to see if the person on worksheet 1 appears in worksheet
2.