Fuzzy lookup in Excel
In this Tutorial we will learn what is Fuzzy lookup, What is fuzzy match, and what is a 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, this 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 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 set up and the installation process, Open you Excel file for which you want to lookup for.
Example of Fuzzy lookup
Here is an example to explain 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 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
Select the data and Press the shortcut CTRL + T
Here we have two table. 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 Fuzzy Lookup Task Pane open. Fill in the Fuzz Lookup Task Pane.
- Left Table indicates the table name from where we want to find the data.
- Right Table indicates the table name for the value we are finding in 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 least similarity and 1 stands for the exact match. Here the threshold limit is set to 0.21 and Click on Go.
You will see the result displayed in the columns you selected. 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.
You might sometimes need to adjust the similarity range to check for the low similarities value.
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 the 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 table so you need to convert your data into 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 perfect match do not exist. Learn more great techniques and become an Excel Superstar.
Hope you like our tutorial for more cool and amazing trick of excel Like our Excel Superstar Facebook Page and subscribe to our Excel Superstar YouTube channel. Excel Superstar is the leading online training company, which provides Online Excel Course in Hindi. Connect with us and become an Excel Superstar.