Excel 2020: Use the Fuzzy Lookup Tool from Microsoft Labs

August 19, 2020 - by Bill Jelen

Excel Use the Fuzzy Lookup Tool from Microsoft Labs. Photo Credit: Dayne Topkin at Unsplash.com

When you use VLOOKUP, HLOOKUP, or INDEX/MATCH, Excel is expecting an exact match. But in real life, data is messy. Several years ago, the research team at Microsoft Labs released a free Fuzzy Lookup add-in. The functionality was never added to Excel, but later showed up in SQL Server. However, the free tool is still available from https://mrx.cl/fuzzylookup

Download and install the add-in. The last step of the install process lets you open the install folder where you will you will find a ReadMe document and a sample Excel file.

Open the sample file. On the Fuzzy Lookup tab, choose Fuzzy Lookup. In the panel that opens, choose the Left Table, the Right Table, and the columns in common.

In the top of the Fuzzy Lookup panel, specify the Left Table is Portfolio. The Right Table is SP500_Date. In the Left Columns box, choose Company. In the Right Columns box, choose Company.

Optionally, choose that you want to see the best 2 or best N matches. Although it is more work, I always ask for at least two matches because Fuzzy Matches are never perfect.

At the bottom of the Fuzzy Lookup, specify 2 as the number of matches. Leave the Similarity Threshold at 50%.

Here are the results. Note if you had asked for only 1 match, you would not see the choice between Coca-Cola Company and Coca-Cola Enterprises. Also - beware of Fuzzy Match algorithms: ATT Corp and ITT Corporation are very similar and could be reported as a match. Someone should always review the matches to determine if they are accurate.

Title Photo: Dayne Topkin at Unsplash.com

This article is an excerpt from MrExcel 2020 - Seeing Excel Clearly.