How to install fuzzy lookup in excel

Disclaimer: This webpage is intended to provide you information about patch announcement for certain specific software products. The information is provided "As Is" without warranty of any kind. The links provided point to pages on the vendors websites. You can get more information by clicking the links to visit the relevant pages on the vendors website.

Table of Contents Show

Step 1: Download Fuzzy Lookup Add-In
Step 2: Enter the Two Datasets
Step 3: Create Tables from Datasets
Step 4: Perform Fuzzy Matching
Where is fuzzy Lookup in Excel 365?
How does Excel fuzzy Lookup work?
How do you use fuzzy Lookup on one column?

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.

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.

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.

Often you may want to join together two datasets in Excel based on imperfectly matching strings. This is sometimes called fuzzy matching.

The easiest way to do so is by using the Fuzzy Lookup Add-In for Excel.

The following step-by-step example shows how to use this Add-in to perform fuzzy matching.

Step 1: Download Fuzzy Lookup Add-In

First, we need to download the Fuzzy Lookup Add-In from Excel.

It’s completely free and downloads in only a few seconds.

To download this Add-In, go to this page from Microsoft and click Download:

Then click the .exe file and follow the instructions to complete the download.

Step 2: Enter the Two Datasets

Next, let’s open Excel and enter the following information for two datasets:

We will perform fuzzy matching to match the team names from the first dataset with the team names in the second dataset.

Step 3: Create Tables from Datasets

Before we can perform fuzzy matching, we must first convert each dataset into a table.

To do so, highlight the cell range A1:B6 and then press Ctrl+L.

In the new window that appears, click OK:

The dataset will be converted into a table with the name Table1:

Repeat the same steps to convert the second dataset into a table with the name Table2:

Step 4: Perform Fuzzy Matching

To perform Fuzzy matching, click the Fuzzy Lookup tab along the top ribbon:

Then click the Fuzzy Lookup icon within this tab to bring up the Fuzzy Lookup panel.

Choose Table1 for the Left Table and Table2 for the Right Table.

Then highlight Team for Left Columns and Team for Right Columns and click the join icon between the boxes, then click Go:

The results of the fuzzy matching will be shown in the cell you currently have active in Excel:

From the results we can see that Excel was able to match each team name between the two datasets except for the Kings.

Excel also shows a Similarity score, which represents the similarity between 0 and 1 of the two names that it matched.

Feel free to adjust the minimum Similarity score within the Fuzzy Lookup panel to allow for matching between text values that have lower similarity scores.

Where is fuzzy Lookup in Excel 365?

Fuzzy Lookup has been added to the Office 365 version of Excel. You can access it from the Merge dialog box ( Data tab , Get Data , Combine Queries , Merge ) and clicking on “Use fuzzy matching to perform the merge” .

How does Excel fuzzy Lookup work?

Excel Fuzzy Lookup Add-In is used to match similar, but not exactly matching data. This function is often used instead of VLOOKUP, when we want to compare two columns which have very similar data, but not exactly the same. As an output, Fuzzy Lookup returns a table of matched similar data in the chosen column.

How do you use fuzzy Lookup on one column?

If you want to find fuzzy duplicates in a single column, select this column or any cell in that column. To fuzzy match within several Excel columns or any block of cells, select them. Сlick the Expand selection icon and have the entire table selected automatically.