Messy Name comparison

I'm open for either a formula or a VBA solution, just looking for the best tool for the job.

I need to compare the names our Vendors send us to our list of employees.

Their names come in the format "Mary Switzer", whereas our names are in the format "Switzer, Mary J.", so of course it can't be a straight lookup. Added to that the human error factor, and often we get "Mary Sizer", "Mary Switter", etc. I know nothing is going to be definite on those variations, but I'm looking for a way to reasonably compare the names from their way to our way. It's relatively easy to reformat from theirs to ours, but what's the best way to get "Exactly the Same", "Close", "Kinda similar", "No Way!"

Thanks in advance!

Using Power Query, I was able to derive a Yes Match and a Maybe

Here is the Mcode for your sample

Please Login or Register  to view this content.

Alan,
I appreciate the help, but we don't have power query yet, either, so can't use that solution today. Tomorrow maybe.

Formula couldn't do much on the Fuzzy match
Please try

=IF(MID(D2,FIND(" ",D2)+1,20)&","&LEFT(D2,FIND(" ",D2)-1)=TRIM(C2),"Exactly the Same",IF(ISNUMBER(SEARCH(MID(D2,FIND(" ",D2)+1,20)&","&LEFT(D2,FIND(" ",D2)-1),C2)),"Close","No way!"))


With Power Query
You may change 0.8 ,0.3 in Blue to set the Threshold between "Close", "Kinda similar" and "No Way!

Please Login or Register  to view this content.

If you are using Excel 2016, it is on the Data Tab and called Get and Transform. Same for 365. It is native to Excel starting with 2016. Look at the link in my signature for further understanding and its use.

Bo_Ry, I think your formula gets me a lot closer to what I'm looking for.

Alansideman, you're right, I DO have Power Query. Unfortunately I haven't done any studying on it yet so don't know yet how to apply what you've provided. Also, when I open the tab linked to your signature it takes me to your profile which, really cool, shows your birthday as January 3rd, which is also my birthday! But I don't see any links about using and understanding Power Query.

This is what I wanted you to click on

https://www.poweredsolutions.co/2015/01/23/power-query/

Ah, thanks for the link. Guess I'll have to dive in and figure out how to apply it to my issue. I appreciate the MCode you provided, and assume, kind of like VBA, that I put this code into Power Query somewhere, as an alternative to performing each step and having Power Query record the steps. Don't know yet where to put it, but I'll research and find out.

1. Create Table by click one cell in data > Press Ctrl+T or Ribbon Insert > Table

2. Ribbon Data > Get data > (New Query) > From other sources > Blank Query

3. At Power Query Ribbon Home > Advanced editor > Paste the M-Code