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

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Our Way", type text}, {"Their Way", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Our Way", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Our Way.1", "Our Way.2"}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Split Column by Delimiter", "Their Way", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, true), {"Their Way.1", "Their Way.2"}),
    #"Split Column by Delimiter2" = Table.SplitColumn(#"Split Column by Delimiter1", "Our Way.2", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, true), {"Our Way.2.1", "Our Way.2.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter2",{{"Our Way.1", type text}, {"Our Way.2.1", type text}, {"Our Way.2.2", type text}, {"Their Way.1", type text}, {"Their Way.2", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type1", "Poss Match", each if[Our Way.1]=[Their Way.2] and [Our Way.2.1]=[Their Way.1] then "Yes" else "Maybe")
in
    #"Added Custom"

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!

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    ChangedType = Table.TransformColumnTypes(Source,{{"Our Way", type text}, {"Their Way", type text}}),    
    Fuzzy = List.Accumulate({1,0.8,0.3},ChangedType, (s,i) =>  
Table.TransformColumns(Table.FuzzyNestedJoin(s, {"Our Way"}, s, {"Their Way"}, Text.From(i), JoinKind.LeftOuter, [IgnoreCase=true, IgnoreSpace=true, Threshold=i]),{Text.From(i),Table.RowCount })),
    AddedMatch = Table.AddColumn(Fuzzy, "Match", each if [1] > 0 then "Exactly the Same" else if [0.8] > 0 then "Close" else if [0.3] > 0 then "Kinda similar" else "No Way!"),
    KeepColumns = Table.SelectColumns(AddedMatch,{"Our Way", "Their Way", "Match"})
in
    KeepColumns

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