Cross reference two worksheets to identify matches

  • Thread starter Thread starter Christie
  • Start date Start date
C

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
 
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?
 
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")
 
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
 
Back
Top