This guide will discuss how to perform fuzzy matching in Excel.
Excel is an excellent tool for organizing, manipulating, and analyzing data. Since it contains several tools and functions we can use to perform certain tasks, we can simply just use the built-in functions to successfully accomplish what we need to do.
However, there are times the built-in functions Excel has are not enough to perform what we want to do. In this case, the VLOOKUP function or XLOOKUP function are some examples of the lookup functions Excel has. So these functions are usually used to look for exact matches or perform range lookups.
But, sometimes, these are not enough, especially when dealing with data sets containing misspelled words or the usage of nicknames. So the built-in lookup functions of Excel would not be able to find the matches. And fuzzy matching refers to looking for an approximate match.
So this is where the fuzzy matching comes in. Although Excel does not have a built-in fuzzy matching function we can readily use, there is a way for us to still perform fuzzy matching in Excel by using the power query editor.
Additionally, we can also use an add-in. So add-ins are additional tools or functions we can install in Excel to deal with the limitations of the built-in functions.
For example, we cannot perform fuzzy matching using the built-in functions. To deal with this limitation, we can install a free add-in that performs fuzzy matching instead.
Let’s take a sample situation wherein we must perform fuzzy matching in Excel.
Suppose you are a researcher working with survey results. So you need to perform a lookup to organize the data. However, some values are misspelled or written differently, making performing the search using a VLOOKUP function impossible.
Worry not! You can simply use the power query editor to perform a fuzzy matching of your data set. For instance, some people wrote DEF Company while others wrote DEF Compnay, Inc.
So the two words are an approximate match meaning the two text strings are almost identical but not necessarily identical. And this would require a fuzzy matching rather than an exact match.
Great! Now let’s move on to a real example of performing fuzzy matching in Excel.
A Real Example of Performing Fuzzy Matching in Excel
Let’s say we are working with a data set containing the results from the company survey. And we want to identify the employees who have not answered the survey. So we have two data sets.
First, we have a table containing the names of the employees. Then, we have another table containing the list of employees who have answered the survey. So our initial data set would look like this:
Since the first table contains the full names of the employees and the second table contains the employees’ nicknames or misspelled names, we need to perform fuzzy matching.
But, we need first to create a translation table to aid it. Basically, we will create a new table containing two columns with the correct name and the nickname used in the survey.
Since some of the nicknames may be too far or different from their proper names, this can help the fuzzy matching be somewhat accurate in its matching.
Secondly, we need to ensure all the data sets or ranges of data are converted into a table. After, we need to name the tables. Then, we will convert the tables to a connection. When we successfully create the three connections, we will have three queries listed.
Lastly, we can finally start performing fuzzy matching in Excel. Afterward, our final data set would look like this:
You can make your own copy of the spreadsheet above using the link attached below.
Amazing! Now we can move on to the steps of how to perform fuzzy matching in Excel.
How to Perform Fuzzy Matching in Excel
In this section, we will explain the step-by-step process of how to perform fuzzy matching in Excel. Moreover, the steps have pictures and detailed instructions to guide you properly.
1. Firstly, we must create a translation table to help the fuzzy matching. So input a new column entitled “From” which will contain the proper names of the employees. Next, we will make another column beside it which will be labeled “To” containing the nicknames of the employees.
2. Secondly, we need to convert all three data sets into a table. To do this, we can simply select the data set and press Ctrl + T. But, we can also go to the Insert tab and click Table. After, check My table has headers and click OK. Next, perform this step on the remaining two data sets.
3. Thirdly, we need to rename each table. So we need to select any cell in a table. Next, we will go to the Table Design tab and rename the table to a unique and relevant name. Then, we will do the same for the other tables and rename them as well.
4. Next, we need to define connections for each table. Firstly, we will select cell B3. Then, we will go to the Data tab and click From Table/Range.
5. Then, the Power Query Editor window will open. So we will go to the Home tab that says Close & Load. After, we will click the drop-down menu below and select Close & Load To.
6. Once the Import Data dialog box is opened, we can click Only Create Connection. Then, we will repeat the same process of creating a connection for the other two tables.
7. Afterward, we can finally perform the fuzzy matching on our tables. Firstly, we will select any blank cell and go to the Data tab. Next, we will select Get Data. From the dropdown menu, we will select Combine Queries. Lastly, we will click Merge.
8. When the Merge dialog box opens, we must select the ProperNames table from the dropdown menu. Then, click the Employee Name heading. In the second dropdown menu, we will select SurveyNames. Similarly, we will click on the Survey Name heading in the data preview.
9. After, we will check the box for Use fuzzy matching to perform the merge.
10. Next, let’s click the triangle to reveal the special settings for fuzzy matching. So we need to scroll to the bottom of the section and set Nicknames as the Transformation Table. Lastly, click OK to perform the merge.
11. In the Power Query Editor, it will show columns for the Employee Name and Survey Names. So we can simply click on the expand icon, which is found at the value of each row, and choose which fields from the SurveyNames table to return.
12. Then, we will go to the Home tab and select Close & Load to display the results. Otherwise, we can select Close & Load To and input a location on the existing worksheet.
13. And tada! We have successfully performed fuzzy matching in Excel.
And that’s pretty much it! We have explained how to perform fuzzy matching in Excel. Now you can apply this learning and use it whenever you need to perform a match for data sets that are not necessarily identical.
Are you interested in learning more about what Excel can do? You can now use the various other Microsoft Excel formulas available to create great worksheets that work for you. Make sure to subscribe to our newsletter to be the first to know about the latest guides and tutorials from us.