Cross reference two worksheets to identify matches

[Christie]

I have two spreadsheets, one with middle names and the other without. I need
a formula that will says its a match even though the middle name is not
shown, for example:

Sheet one - Surname: Smith (A1), Christian name: Michael John
Sheet two - Surname: Smith (A1), Christian name: Michael

After this narrows it down I would need to match the address on both
spreadsheets.

Can anyone help????

Christie

 

[JBeaucaire]

On the sheet with the middle names, too, in column C or D, enter this array
formula for row2:

=IF(ISERROR(MATCH(A2&LEFT(B2,FIND("
",B2)-1),Sheet2!$A$2:$A$400&Sheet2!$B$2:$B$400,0)),"No Match","Match")

Be sure to confirm the formula with CTRL-SHIFT-ENTER or you will receive an
error. If the first name/last name on sheet 2 is anywhere in the first 400
rows on Sheet2, the word "Match" will appear, else "No Match".

Will that work for you?

 

[JBeaucaire]

Here's the formula again, so you can see the piece clearly (internet breaks
things oddly sometimes). Just remove the line feeds at the end to make one
long line:

=IF(ISERROR(MATCH(
A2&LEFT(B2,FIND(" ",B2)-1),
Sheet2!$A$2:$A$400&
Sheet2!$B$2:$B$400,0)),
"No Match","Match")

 

[Christie]

It all came up as matches so it didn't work.
Would you recommend something else?

 

[JBeaucaire]

You can upload your workbook, or a smaller sample of it and I'll look at it.
The formula works, so something's just gotten lost in the translation.

Post it on a forum that allows attachments, like here:

http://www.excelforum.com/excel-general/

You can post a link back here to the thread you start with your sample, or
send me a message on that forum, same username JBeaucaire