How to Perform Fuzzy Matching in Excel
Fuzzy matching in Excel refers to finding approximate matches or partial matches between two sets of data. Microsoft Excel does not have a built-in fuzzy match function, but you can use a combination of functions to perform fuzzy matching.
Here's a step-by-step guide on how to perform fuzzy matching in Excel:
- Install the Fuzzy Lookup Add-In for Excel (for Excel 2010 and 2013 only):
- Fuzzy Lookup Add-In is a free add-on by Microsoft that helps you perform fuzzy matching in Excel. Unfortunately, it only works for Excel 2010 and Excel 2013. You can download it from the official Microsoft website here.
- After downloading, install the add-in and restart Excel. You should see a new tab called "Fuzzy Lookup" in the Excel ribbon.
- Prepare your data:
- Make sure your data is formatted as a table. To convert your data into a table, click anywhere within your data range, and then press
Ctrl + T. In the "Create Table" dialog box, check "My table has headers" if your data has headers, and click "OK".
- Perform Fuzzy Matching using the Fuzzy Lookup Add-In:
Example
Assume we have two tables, Table1 and Table2, with the following data:
Table1:
| ID | Name |
|---|---|
| 1 | John Smith |
| 2 | Jane Doe |
| 3 | Mike Johnson |
Table2:
| ID | Name |
|---|---|
| A | Jon Smth |
| B | Jane Do |
| C | Michael Jonson |
We want to find approximate matches between the names in Table1 and Table2.
- Click the "Fuzzy Lookup" tab in the Excel ribbon.
- In the "Fuzzy Lookup" pane, select Table1 as the "Left Table" and Table2 as the "Right Table".
- In the "Match Columns" section, select the columns you want to match (in this case, "Name" from both tables).
- Set the similarity threshold. The threshold value ranges from 0 to 1, with 1 being an exact match and 0 being no match. Choose a value based on how strict you want the matching to be. For this example, we will use 0.8.
- Click "Go" to run the fuzzy matching process.
- The results will be displayed in a new worksheet. You will see the matched records along with a similarity score.
| Left_ID | Left_Name | Right_ID | Right_Name | Similarity |
|---|---|---|---|---|
| 1 | John Smith | A | Jon Smth | 0.87 |
| 2 | Jane Doe | B | Jane Do | 0.92 |
| 3 | Mike Johnson | C | Michael Jonson | 0.88 |
In this example, the Fuzzy Lookup Add-In found approximate matches between the names in Table1 and Table2 with a similarity score above the threshold (0.8).
Did you find this useful?