How to use Fuzzy Lookup or Fuzzy Match in Excel

In your professional life, you can face a situation like, you have a Source Data Table (Table 1), where Shop_ID, Shop Name etc. field are available like below image:
 
 
                                                Image 1: Source Data Table

And you have a Target Data Table (Table 2) where you have to lookup the Shop_ID from Source Data Table to prepare a final report to your Management. Here is the second table (Table 2), You need to include Shop_ID column in Table 2 and add the Shop_ID besides the Shop name by matching partial text.


For Example:

BW Corp from Table 2 will match BW Corporate in Shop Name column in Table 1 and returns the Shop_ID which is STR002 value besides BW Corp from Table 1 to Table 2. 
 

Image 2: Target Data Table

Now what will you do to prepare the report? Well, I guess 90% people will go through Ctrl+F (Find) then Ctrl+C and then Ctrl+V process. But do you know that, there is a wonderful add-ins developed by Microsoft itself to match approximately value and pull from source data which is free. Well, it is Fuzzy Lookup.

Fuzzy Lookup add-ins developed for Microsoft Excel by Microsoft to match a cell value partially and if matched then returns that cell value. It is possible to apply a formula also that works like Fuzzy Lookup Add-Ins.


How to Use Fuzzy Lookup Add-ins:

Step 1:
First you need to download this add-ins from Microsoft.com, here is the link:

https://www.microsoft.com/en-us/download/details.aspx?id=15011

System Requirement:
- Microsoft Excel 2007, 2010 or 2013.
- Microsoft Excel 2016 will not support.

Step 2:
After download the exe file, run Setup.exe as Administrator. To do this, just Right Click on the Setup.exe icon which you have downloaded, and then Click on Run as Administrator.

Step 3:

Click on Install Button.

Image 3: Fuzzy Lookup Add-In for Excel Setup

Step 4:

Check "I accept the terms in the License Agreement" and Click on Advanced button.
 

Image 4: I accept the terms in the license agreement

Step 5:
Select "Install for all users of this machine". Click on Ok.
 

Image 5: Install for all users of this machine option

This will install the add-in in your Microsoft Excel and show a dialog box after complete the installation:
 

Image 6: Installation Complete

Step 6:
Click on Finish button. This will show the folder location where it installed. Click the Close button:
 

Image 7: Setup Successful

Steps 7:

After successfully installation, Restart your Microsoft Windows and then run Microsoft Excel for better installation process. You will see a Fuzzy Lookup menu in your Menu bar, like below image:
 

Image 8: Fuzzy Lookup menu

Step 8:
Now before working on Fuzzy Lookup, you need to convert 2 data tables into Table format. To do this Click anywhere in the Source Data Table (Table 1) and Press Ctrl+T and then press Enter. This will convert the Source Data Table (Table 1) into Table format and named as Table 1 automatically. By this way convert the Target Data Table (Table 2) into Table format and named as Table 2.

Step 9:
Now, beside the Target Data Table (Table 2), Select a cell from where you want to view the report. Assume that you wish to see your result table in D3 cell. So, Click on D3 cell to Select it.
 

Image 9: Before starting Fuzzy Lookup, Select a cell to show the report there. Here D3 is that cell.

Step 10:
Click on Fuzzy Lookup | Fuzzy Lookup. This will show a Task Pane named Fuzzy Lookup.
 

Image 10: Fuzzy Lookup task pane

Settings of Fuzzy Lookup:

There are 4 parts (as I understood) in Fuzzy Lookup.


Part 1: Table Selection:
The first part is table to table matching. Under this part there are 2 items.

(a) Left Table:
First of all under Left Table, Click the drop down arrow. You will see 2 names are present here. Table1 and Table2 which you have just converted in Step 8. Select the Source Table (Table1).

(b) Right Table: 
Under Right Table, Select the Target Table (Table2).

Part 2: Column Selection:
The second part is column to column matching. Under this part there are 2 items.
 
(a) Left Column:
As you want to match the Shop_Name from Table1 to Shop column of Table2. Double Click on Shop Name column from Left Columns. This will insert the Shop Name column in Match Column section.
 
(b) Right Column:
Again Double Click on Shop column from Right Columns. This will insert the Shop column in Match Column section.


 Part 3: Making Connection:
The third part is making connection. Under this part there are 2 items.
 
(a) Make Connection:
This is the main connection of Fuzzy Lookup. After inserting the Left and Right Column, Click the Fuzzy Lookup Building icon (in between Left Columns and Right Columns).
 

Image 11: Fuzzy Lookup Building icon

(b) Match Columns:
After clicking on Fuzzy Lookup Building icon you will see the Left Columns name and Right Columns name in this preview and also the Configuration status under Match Columns box.

 
Part 4: Output Settings:
The 4th part is about output setting. Under this part there are 3 items.

(a) Output Columns:
The Output Columns helps you to customize output table. You can choose the Columns and Tables which you need in your desired report. For example here you need to generate a report with 3 columns. These are: Table1.Shop_ID column, Table2.Shop column, Table2.Sales Value column. Just Check these columns and Uncheck others columns.
 

Click the Go button to generate a report.

(b) Number of Matches:
This is an advance option. After viewing the result, in Table2 the SS Tel found 2 times with 2 different values. Now if you set the Number of Matches: 2, then Fuzzy Lookup will return every record of Table 2 maximum twice and the shop SS Tel which has already twice in Table2 with 2 different values, will return two or more times but 2 different values.

The point is if your table contains same Shop Name but 2 or more different values and you wish to lookup the Shop_ID beside this, then you can increase the Number of Matches. We will set 1 for above example only, but you can set it as per your requirement.

(c) Similarity Threshold:
This is another advance option. Depends on your Target Data, you need to decide a number in between 0.0 to 1.0.

0 (Zero) is 0% match with Table1.Shop_Name with Table2.Shop.

And you will see the maximum matching result with this setting. If you set 1 then it means 100% match with Table1.Shop_Name with Table2.Shop. If you choose in between like 0.20, 0.35, 0.65 etc. then you will find that, every time you increase the matching percentage, your matching result decreases. You need to test different Similarity Threshold and check your desire result. Here in this example for our data table, we will select 0.09 (Which is 9%).

Step 10:
Your settings of Fuzzy Lookup will finally looks like below. Click on Go button again for result.
 

Image 12: Fuzzy Lookup Settings and Result

Note that:

1. In the above 2 data table, Shop Name is not match exactly, like "ABC Tel" and "ABC Telecom" is not same.
2. You have to determine the percentage of match and test the result each time. Why each time? Because depends on your data Fuzzy Lookup will give you a choice about match percentage.
3. Fuzzy Lookup is a tool that shows the result as like as PivotTable. You can test the result by changing percentage of match.

Now you will see the result from D3 cell. It's such a wonderful add-ins which helps a lot for those who works with manual data (collected from Field through email). I would like to recommend, this add-ins for compiling a data table collected from different sources.
|||| Please SUBSCRIBE our YouTube Channel ||||
https://www.youtube.com/channel/UCIWaA5KCwZzBGwtmGIOFjQw