The Fuzzy Lookup Add-In for Excel performs fuzzy matching of textual data in Excel. Its a free excel plugin and is available to download here.
History
The Fuzzy Lookup Add-In for Excel was developed by Microsoft Research and performs fuzzy matching of textual data in Microsoft Excel. It can be used to identify fuzzy duplicate rows within a single table or to fuzzy join similar rows between two different tables. The matching is robust to a wide variety of errors including spelling mistakes, abbreviations, synonyms and added/missing data. For instance, it might detect that the rows “Mr. Andrew Hill”, “Hill, Andrew R.” and “Andy Hill” all refer to the same underlying entity, returning a similarity score along with each match. While the default configuration works well for a wide variety of textual data, such as product names or customer addresses, the matching may also be customized for specific domains or languages.
Use
Fuzzy Lookup utilizes advanced mathematics to calculate the probability that what it finds matches up with your search entry, which means the tool works even when characters (numbers, letters, punctuation) do not match up exactly. Think of it as a beefier version of VLOOKUP that is more flexible and even easier to use. There are many uses of the tool, at its simplest you can do the Vlookup but quickly bring in all columns not just a single one. Its core is in similarity matching though and this is a real gem especially if you are trying to sort through text data for matchings.
I initially found it when I was doing a product matching exercise , matching product code from a supplier to a catalogue , great if you have clean data and the same supplier, but what if you want to match one supplier product to another supplier’s product?
What if you want to do this often with different products and suppliers every exercise?
Well Fuzzy lookup has got your back!
If you want to create a mapping table between two fields but there is no unique key and you need to collate a join?
Well Fuzzy lookup has got your back!
| Supplier Name | Mapped Supplier | Similarity |
|---|---|---|
| Amazon Limited | Amazon Ltd | 0.8 |
| Big Medical Group PLC | Big Medi Group PLC | 0.89 |
| Amazon | Amazon Ltd | 0.78 |
The above table shows how you can quickly find the closed match between two data sets based on similarity percentage.
If you just wanted 100% match you just set it to 100% match and you bring in the whole table , or you set an internal threshold of similarity and anything above that will be a match if available!
The time saved doing Orthopaedic Product Matching for 200+ Trusts across 1000’s products and 10’s of suppliers has justified itself on my default install list.
Full instructions on how to use the plugin are available via the download link as well as a sample case once you install it. Get your IT administrator to enable the download
