Matching Names with different criteria

[kittymongo]

I need help with a formula or set of formulas to match 2 columns of names. One column will have first and last name and the second column will have last comma first name and sometimes a middle initial. How can I compare the 2 to match the data sets without the middle names, suffixes, etc. I only want first and last name matches.

Jane Smith	Smith, Jane A
John Snow	Snow, John Wick

<colgroup><col><col></colgroup><tbody>
</tbody>

Thank you -

 

[njimack]

Re: Need Excel Formula Help - Matching Names with different criteria

=isnumber(search(right(a2,len(a2)-find(" ",a2))&", "&left(a2,find(" ",a2)-1),b2))

 

[kittymongo]

Re: Need Excel Formula Help - Matching Names with different criteria

Thank you so much! This worked perfectly! Life saver.

 

[kittymongo]

OK, I have one more question. We just received a file to process and need to do the same name matching but this formula is not working because now we have first name, middle name, last name in column one and second column has last name comma first name middle initial or middle name. How can I tweak the formula to match the first and last names only between the 2 data sets?

I really appreciate your help with this.

 

[kittymongo]

Re: Need Excel Formula Help - Matching Names with different criteria

=isnumber(search(right(a2,len(a2)-find(" ",a2))&", "&left(a2,find(" ",a2)-1),b2))

OK, I have one more question. We just received a file to process and need to do the same name matching but this formula is not working because now we have first name, middle name, last name in column one and second column has last name comma first name middle initial or middle name. How can I tweak the formula to match the first and last names only between the 2 data sets?

I really appreciate your help with this.