If you are trying to have partial matching besides the exact matching of different data, you can use Fuzzy Lookup Excel for this purpose. This article will give you the introduction and the procedures of this Fuzzy Lookup feature of Excel.
Purpose of Fuzzy Lookup Excel
Using the Fuzzy Lookup feature of Excel you can indicate the partial matches of two data tables, moreover, you can try the exact match also using this feature.
Here, we have two datasets containing sales records for January and February of XYZ company. Using these datasets we will find out the similarities between the Product and SalesPerson columns of these two data ranges.
Download Link of the Fuzzy Lookup Add-In
Firstly, you have to install this add-in by clicking the following link.
After the completion of installation, when you open your Excel workbook this feature will be added automatically. Here, we can see that we have a new tab named Fuzzy Lookup which has the Fuzzy Lookup Option.
In this article, we will try to show the steps of using the Fuzzy Lookup feature of Excel along with the fuzzy matching option of Power Query to indicate the partial matching of two data tables.
We have used the Microsoft Excel 365 version here, you can use any other version according to your convenience.
1. Using Fuzzy Lookup Add-In
Step-01: Creation of Two Tables for Fuzzy Lookup Excel
Prior to using the Fuzzy Lookup option we have to convert the following two data ranges into two different tables.
Following the article “How to Make a Table in Excel” we have converted the ranges into these tables.
Now, we have to rename these tables.
➤ Select the table for Sales Record of January and then go to Table Design Tab >> rename the Table Name as January.
Similarly, rename the Sales Record of February table as February.
Step-02: Creating Fuzzy Lookup with Fuzzy Lookup Excel Add-In
➤ Go to Fuzzy Lookup Tab >> Fuzzy Lookup Option.
Now, you will get a Fuzzy Lookup portion on the right pane.
➤ Select the cell where you want your output comparison table.
➤ Choose the Left Table as January and the Right Table as February.
Now, we have to select the columns on the basis of which we want this comparison, as we want this comparison on the basis of the Product column and the SalesPerson column so these columns are selected in the Left Columns and Right Columns boxes.
As Output Columns select the January.Product and January.SalesPerson from the January table and,
February.Product and Febuary.SalesPerson from the February table and finally,
Select the FuzzyLookup.Similarity for getting the percentage indication of similarities.
For this step, we selected the Number of Matches as 1 and the Similarity Threshold as 0.51 and then pressed Go.
In this way, we have got matches for the Products Apple and Green Apple for the SalesPerson Howard and for Cherry, Blackberry, and Tomato which are fully matched as the similarity is 100%.
Effects of Changing Number of Matches and Similarity Threshold
Number of Matches:
Depending on the selection of this option, we will get the highest number of matches.
For selecting the Number of Matches as 1,
we are getting the following comparison table where we have one similarity for each product, but we had Blackberry 2 times in the February table with different SalesPersons.
But if you select the Number of Matches as 2,
then you will get the matching results for these two Blackberry products with the SalesPerson Sheldon and Matt.
Similarity Threshold:
It has a range between 0 to 1 and for going from the lower range to the higher range, we will move from partial match to exact match.
Firstly, we will try with a Similarity Threshold of 0.1.
Here, we are getting the similarities from 20% to 100%.
For selecting the Similarity Threshold as 0.4,
the similarity range is from 60% to 100%.
When we have selected the Similarity Threshold range as 0.84,
Then the similarity range is from 90% to 100%.
Finally, for selecting the highest Similarity Threshold range like 1,
Then you will get only the exact matches as the similarity range is 100%.
2. Power Query Fuzzy Matching Option
Here, we use Power Query for partial matching of two data ranges instead of the Fuzzy Lookup option.
Step-01: Creation of Two Queries
For comparing the Product and SalesPerson columns of the January and February sales records at first we will convert these two ranges into queries.
➤ Go to Data Tab >> From Table/Range option.
Then the Create Table wizard will pop up.
➤ Select the range of your data table (here, we are selecting the data range of the Sales Record of January)
➤ Check My table has headers option and press OK.
After that, a Power Query editor will open up.
➤ Rename the query as January.
Now, we will import this data as a connection only.
➤ Go to Home Tab >> Close & Load Dropdown >> Close & Load To option.
Then, the Import Data dialog box will appear.
➤ Click on the Only Create Connection option and press OK.
Similarly, create a query named February for the dataset Sales Record of February.
On the right pane, we can see the name of the two queries January and February, which we have created in this step.
Step-02: Combining Queries for Fuzzy Lookup Excel
In this step, we will combine the queries of the previous step to match the data of these queries.
➤ Go to Data Tab >> Get Data Dropdown >> Combine Queries Dropdown >> Merge Option.
Afterward, the Merge wizard will pop up.
➤ Click the dropdown of the first box and then select the January option.
➤ Select the dropdown of the second box and then select the February option.
After that, we have to select the columns of the two queries by pressing CTRL with a Left-click at a time on the basis of which we want to match our data.
Then, we can see that it has found 3 rows matching from 9 rows.
Step-03: Using the Fuzzy Matching Option for Fuzzy Lookup Excel
Now, we will use the Fuzzy Matching option to perform the partial matching besides the exact matches.
➤ Check the Use fuzzy matching to perform the merge option and then select the Similarity threshold as 0.5 for this option.
➤ Select the Ignore case option and the Match by combining text parts option.
For this step, we have selected the Maximum number of matches as 1 and pressed OK.
Here, we can see the matching number has been increased from 3 to 5.
Then, you will be taken to the Power Query Editor window.
Here, we can see the first two columns from the January query but the columns of the February query are hidden. So, we have to expand this February column.
➤ Click on the indicated sign beside February.
➤ Select the Expand option and press OK.
Now, we can see the matches of the two queries properly.
Effects of Changing Similarity Threshold
If we change the Similarity threshold from 0.5 to 0.2, then we will have 8 matches in the place of 5 matches.
After pressing OK, we can see that except for the first row, the other rows are partially similar to each other.
For selecting the Similarity threshold from 0.2 to 1, then we will have 4 matches in the place of 8 matches.
So, for the exact matches ignoring cases only we are having the results this time.
Things to Remember
🔺 The built-in lookup functions like the VLOOKUP function, HLOOKUP function are useful for exact matching cases, but for finding approximate matches according to our wish we can use the Fuzzy Lookup add-in of Excel.
🔺 To produce different results for matching cases, you can change the Number of Matches and Similarity Threshold parameters as per your necessity.
Practice Section
For doing practice by yourself we have provided a practice section like below in a sheet named practice. Please do it by yourself.
Download Practice Workbook
Conclusion
In this article, we tried to cover the using procedures of the feature Fuzzy Lookup Excel. Hope you will find it useful. If you have any suggestions or questions, feel free to share them in the comment section.
<< Go Back to Lookup | Formula List | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
