Fuzzy Text Search with Fuzzy Lookup in Excel

One of the most frustrating situations that a user may encounter while working in Microsoft Excel is searching and substituting data with an inexact match. When you need to substitute data from one table to another, but you are sure that the matching elements are named the same in both tables, then there are no problems – there are many ways at your service: VLOOKUP functions and its analogues, Power Query add-in, etc.

But if in one table “Pupkin Vasily”, and in the other just “Pupkin”, or “Pupkin V.”, or even “Navelеn”, then all these beautiful ways do not work. Moreover, in practice, this occurs all the time, especially with postal addresses or company names:

Pay attention to the different types of inconsistencies that may occur:

• rearranged places street, city, house
• some part of the address is missing or, on the contrary, there is something extra (zip code, apartment number)
  
• the city (with or without the letter “g.”) or street is written differently
  
• typos and errors (KоZan instead of Kazan)
  

There is no need to talk about an exact match or even a search by mask. In this case, only special macros or add-ins for Excel can help. I already wrote about one of these macro functions in VBA, but here I want to talk about another option for solving a similar problem – an add-in Fuzzy Lookup from Microsoft companies.

This add-on has been around since 2011 and is completely free to download from the Microsoft website. System requirements: Windows 7 or later, Office 2007 or later respectively. After installation, a tab with the same name appears in Excel with a single button on it:

Clicking this button turns on a special panel on the right side of the Excel window, where all search settings are set:

I want to note right away that this add-in can only work with smart tables, so all source tables need to be converted to smart tables using the combination Ctrl+T or buttons Format as a table on the Home tab (Home — Format as Table):

The algorithm of actions when working with the Fuzzy Lookup add-in is as follows:

1. Select which tables you want to link in the dropdown lists Left и Right Table.
   
2. Select the key columns in the left and right tables for which you want to check the match and click the button to add the created pair to the list Match Columns
   
3. The list Output Columns check the boxes for the columns you want to output as a result.
   
4. Set the active cell to an empty place on the sheet where you want to display data
   
5. Нажмите кнопку Go

After analysis, we get a table where each element of the key column from the first table is matched with the most similar value from the second:

Lepote!

Nuances and pitfalls

• Selection accuracy can be adjusted with the slider Similarity Threshold at the bottom of the Fuzzy Lookup panel. The more to the right its position, the stricter the search will be, and – as a result – the less results the add-in will find. If you move it to the left, then there will be more results, but the risk of an erroneous match will increase. It all depends on your specific situation – experiment.
• On large tables, the search can take decent amount of time (up to several tens of seconds), although a lot, of course, depends on the power of your computer. Alternatively, to speed up in the settings (button Confige at the bottom of the panel) you can try to enable the parameter UseApproximateIndexing in section Global Settings.
  
• Before pressing the button Go don’t forget to select an empty cell, starting from which you want to display the results. If by chance you leave an active cell somewhere in the original data, then the add-in will display the final table right on top of them, and you will lose them. Moreover, the cancellation of the last action will be impossible, and the Undo button at the bottom of the panel does not always work for some reason.
  
• To display a column with a similarity coefficient FuzzyLookup.Similarity it is required that your excel has a dot as decimal separator (integer and fractional part). If this is not the case, then this setting can be temporarily changed via File – Options – Advanced (File — Options — Advanced).
  
• Fuzzy Lookup is not a regular VBA add-in (like my PLEX, for example), but a COM add-in. The difference is that she installed as a separate program, i.e. you need the appropriate rights to install the software on your computer. At home, of course, there will be no problems, but many corporate users will most likely have to contact your IT specialists. After installation, you can disconnect and connect it in the future on the tab Developer – COM Add-Ins (Developer — COM Add-ins).

In any case, with all the disadvantages, this add-in is definitely worth it to be in the arsenal of any advanced Microsoft Excel user.