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.
Additional Resources
The following tutorials explain how to perform other common tasks in Excel:
How to Count Frequency of Text in Excel
How to Check if Cell Contains Text from List in Excel
How to Calculate Average If Cell Contains Text in Excel
