MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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.


Bill Jelen is the author / co-author of
MrExcel 2020 - Seeing Excel Clearly

This is a 4th edition of MrExcel LX. Updates for 2020 include: Ask a question about your data, XLOOKUP, Power Query's Data Profiling tools, How Geography Data Types decide which Madison, A SEQUENCE example for descending 52 weeks, Exchange Rates support in Stock Data Types, How to collapse the Search box, How to leave effective feedback for Microsoft, How to post your worksheet to the MrExcel Board using XL2BB.