Fuzzy Lookup in Excel (With Add-In & Power Query)

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 using procedures of this Fuzzy Lookup feature of Excel.

Download Workbook


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 of 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.

fuzzy lookup Excel


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 then 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.

fuzzy lookup Excel


Procedures of Using Fuzzy Lookup Excel

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 Microsoft Excel 365 version here, you can use any other versions 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.

fuzzy lookup Excel

Following the article “How to Make a Table in Excel” we have converted the ranges into these tables.

creating two 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.

creating two tables

Similarly, rename the Sales Record of February table as February.

fuzzy lookup Excel


Step-02: Creating Fuzzy Lookup with Fuzzy Lookup Excel Add-In

➤ Go to Fuzzy Lookup Tab >> Fuzzy Lookup Option.

creating fuzzy lookup Excel

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.

creating fuzzy lookup Excel

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.

creating fuzzy lookup Excel

As Output Columns select the January.Product and January.SalesPerson from the January table and,

creating fuzzy lookup Excel

February.Product and Febuary.SalesPerson from the February table and finally,

creating fuzzy lookup Excel

select the FuzzyLookup.Similarity for getting the percentage indication of similarities.

creating fuzzy lookup Excel

For this step, we have selected the Number of Matches as 1 and the Similarity Threshold as 0.51 and then pressed Go.

creating fuzzy lookup Excel

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%.

creating fuzzy lookup Excel


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,

fuzzy lookup Excel

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.

number of matches

But if you select the Number of Matches as 2,

number of matches

then you will get the matching results for these two Blackberry products with the SalesPerson Sheldon and Matt.

number of matches

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.

similarity threshold

Here, we are getting the similarities from 20% to 100%.

similarity threshold

For selecting Similarity Threshold as 0.4,

similarity threshold

the similarity range is from 60% to 100%.

similarity threshold

When we have selected the Similarity Threshold range as 0.84,

similarity threshold

Then the similarity range is from 90% to 100%.

similarity threshold

Finally, for selecting the highest Similarity Threshold range like 1,

similarity threshold

Then you will get only the exact matches as the similarity range is here 100%.

fuzzy lookup Excel


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.

fuzzy lookup Excel

➤ Go to Data Tab >> From Table/Range option.

creation of two queries

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.

creation of two queries

After that, a Power Query editor will open up.
➤ Rename the query as January.

creation of two queries

Now, we will import this data as a connection only.
➤ Go to Home Tab >> Close & Load Dropdown >> Close & Load To option.

creation of two queries

Then, the Import Data dialog box will appear.
➤ Click on the Only Create Connection option and press OK.

creation of two queries

Similarly, create a query named February for the dataset Sales Record of February.

creation of two queries

On the right pane, we can see the name of the two queries January and February, which we have created in this step.

fuzzy lookup Excel

Related Content: VLOOKUP Approximate Match Text in Excel (4 Examples)


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.

fuzzy lookup Excel

Afterward, the Merge wizard will pop up.

combining queries

➤ Click the dropdown of the first box and then select the January option.

combining queries

➤ Select the dropdown of the second box and then select the February option.

combining queries

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.

fuzzy lookup Excel

Then, we can see that it has found 3 rows matching from 9 rows.


Similar Readings: 


Step-03: Using 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.

fuzzy lookup Excel

➤ Select the Ignore case option and the Match by combining text parts option.

using fuzzy matching 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.

using fuzzy matching option

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 besides February.

using fuzzy matching option

➤ Select the Expand option and press OK.

using fuzzy matching option

Now, we can see the matches of the two queries properly.

fuzzy lookup Excel


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.

fuzzy lookup Excel

After pressing OK, we can see that except for the first row the other rows are partially similar to each other.

using fuzzy matching option

For selecting the Similarity threshold from 0.2 to 1, then we will have 4 matches in the place of 8 matches.

similarity threshold

So, for the exact matches ignoring cases only we are having the results this time.

fuzzy lookup Excel

Related Content: Excel SUMIF with Partial Match (3 Ways)


Things to Remember

🔺 The built-in lookup functions like the VLOOKUP function, HLOOKUP function is 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.

practice


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.


Related Articles

Tanjima Hossain

Tanjima Hossain

Hello everyone, This is Tanjima Hossain. I have completed my graduation from BUET. Then I have started working as a technical writer in SOFTEKO. I have grown interest in technical content writing, research topics, numerical analysis related field and so I am here. Besides this I love to interact with different people and I love to spend my spare time by reading, gardening ,cooking etc.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo