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

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

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")


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

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

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

"JBeaucaire" wrote:

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")


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

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

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 he

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

--
"Actually, I *am* a rocket scientist." -- JB

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


"Christie" wrote:

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

"JBeaucaire" wrote:

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")


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

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