Fuzzy Lookup

It still seems to be OK for me!!

However there may be alternatives. Please post a sample sheet with a few rows of representative data.

Here's a stonker of a formula (from Bo_Ry):

=LOOKUP(1,1/FREQUENCY(0,1/MMULT(1-ISERR(SEARCH(TRIM(MID(SUBSTITUTE(A2," ",REPT(" ",50)),{0,1,2,3,4}*50+1,50)),'Table 1'!$A$2:$A$12)),{1;1;1;1;1})),'Table 1'!$B$2:$B$12)

I'll be honest and say I don't fully understand it myself yet!!

Originally Posted by Glenn Kennedy

Here's a stonker of a formula (from Bo_Ry)

The formula is not a really good Fuzzy match.
It split word by comma and search all the words in the Lookup table1.

eg: Add this name "Rah, AHSA" to table2 and "Rah, AHS" to Table1

split word by comma to "Rah" and "AHSA" and search in Table2

Search Rah found in "RAH, IHTESM-UR" and "Rah, AHS"
But the formula doesn't know that "AHSA" is a closer match to "AHS".
It just returns the first match which is "RAH, IHTESM-UR" 11111


The Better way is to use Power Query Fuzzy Match
https://support.microsoft.com/en-us/...4-bd3962b90649

Create Table1 and Table2 , Ctrl+T


Open Power Query/Get and Transform. Click on New Query.

Open blank query in the editor, launch Advanced Editor and paste in the following code.

let
    Source = Table.FuzzyNestedJoin( Table.TransformColumnTypes(Excel.CurrentWorkbook(){[Name="Table2"]}[Content],{{"Name", type text}}), {"Name"}, Table.TransformColumnTypes(Excel.CurrentWorkbook(){[Name="Table1"]}[Content],{{"EPIC PROVIDER NAME", type text}}), {"EPIC PROVIDER NAME"}, "Table1", JoinKind.LeftOuter, [IgnoreCase=true, IgnoreSpace=true, NumberOfMatches=1,Threshold=0.3]),
    Expanded = Table.ExpandTableColumn(Source, "Table1", {"EPIC PROVIDER NAME", "Employee #"})
in
    Expanded