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?

--
"Actually, I AM a rocket scientist." -- JB

Your feedback is appreciated, click YES if this post helped you.


"Christie" wrote:

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