In this tutorial, we will learn what is Fuzzy lookup, What is a fuzzy match, and what is the use of fuzzy logic.
While searching for matching results we will look for the values by using the VLOOKUP, HLOOKUP, and the Match function in Excel. As you know, all functions are used when our purpose is to match exactly with the result. In other words, we used this function when we want to find the exact match.
But, sometimes it might happen that you might have created some spelling error while entering the data in the Excel spreadsheet or there might be slight dissimilarity in the data we are looking for and matching with. In that case, the VLOOKUP, HLOOKUP, and MATCH function won’t work.
You might be thinking what is the other function or formula that can help me to carry out my required task.
Well, there is no function or formula to carry out the task. But Microsoft has introduced a new-add in that will help you to find similar data in no time. The name of the Add-In is Fuzzy Lookup. Fuzzy Lookup will help you to find similar matching data which just a few clicks.
You need to download the Add-in in Excel. It is available free of cost. To download the Fuzzy Lookup Click Here
After you have downloaded and done with the setup and the installation process, Open your Excel file at which you want to lookup.
Example of Fuzzy lookup
Here is an example to explain to you how can you carry out Fuzzy Lookup in Excel. We have a list of Client Name along with the amount. We want to find few names for which we will give the header as Customer Name.
We want to do a fuzzy lookup for Customer Name in the Client Name. The data for which we want to do Fuzzy Lookup may or may not be in the same worksheet.
Steps to carry out the Fuzzy Lookup in Excel are as follow:
Step 1: First Convert your Data into Table form by selecting the data and then Go to Insert Tab > Click on Table
OR
Select the data and Press the shortcut CTRL + T
Here we have two tables. The table on the left is the Data in which we want to find and the table to the right is what are we looking for.
Step 2: Click on the cell where you want Result to appear.
Step 3: Click on the Fuzzy Lookup Tab > Fuzzy Lookup.
Step 4: To the right, you will see the Fuzzy Lookup Task Pane open. Fill in the Fuzzy Lookup Task Pane.
- The left Table indicates the table name from where we want to find the data.
- The right Table indicates the table name for the value we are finding in the Left table
- Click on the Relationship button that is between the Left Column and the Right Column.
Step 5: Adjust the Similarity Threshold. The Similarity threshold is the range starting from 0 to 1. 0 stands for the least similarity and 1 stands for the exact match. Here the threshold limit is set to 0.21 and Click on Go.
Result:
You will see the result displayed in the columns you selected. On the left side, you will see the original data and to the right, you will find the names you are looking for along with the dissimilarities.
Why Should I learn to use Fuzzy Lookup?
1. There comes a need when you want to check data but there might be some dissimilarity in the name or the order of the name or some spelling error. If you did not use this Microsoft Add-in then you might have gone through each data one after the other.
2. It helps you to save the time and the effort you take for searching almost similar data.
3. It is simple to understand and it is very quick. The steps are very simple and the result is too quick.
4. You can vary the level of similarity you want to find. 0 states for the least similarity and 1 states for Exact match.
5. Impress your boss and other office colleagues with this technique so that they are impressed with it and that will affect your growth in the company.
POINTS TO REMEMBER:
1. Fuzzy Lookup is great for large data sets and perfect for those when the entries are difficult to read.
2. Fuzzy Lookup only works for a table so you need to convert your data into the table before carrying out Fuzzy Lookup.
3. The dissimilarity column is added when you click on the Go button. This is just for identifying the percentage match.
When we are faced with the task of matching the not exact data remember FUZZY LOOKUP in Excel. This will help you in cleaning your data and become more powerful. This becomes an efficient mode for matching data when a perfect match does not exist. Learn more great techniques and become an Excel Superstar.