Zehad Rian Jim
This tutorial will demonstrate how to use the fuzzy LOOKUP algorithm in Excel. It is extremely useful in case we want to find similar matches in Excel. Fuzzy LOOKUP can easily find the similarity within data. It uses advanced maths to compute the probability of finding matches. Moreover, it helps to either compute similarity within a row or merge similar rows from different tables within the same worksheet or different worksheets. By this, we can save a lot of time and ease the work. So, it is essential to use the fuzzy LOOKUP algorithm in Excel.
Download Practice Workbook
You can download the practice workbook from here.
2 Easy Examples to Use Fuzzy LOOKUP Algorithm in Excel
Now, we have two examples to use the fuzzy LOOKUP algorithm in Excel. The first example will include adding the Fuzzy LOOKUP Add-in feature and the second example consists of using Fuzzy Merge in Excel. We’ll use a sample dataset overview as an example in Excel to understand easily. If you follow the steps correctly, you should learn how to use the fuzzy LOOKUP algorithm in Excel on your own.
1. Matching Obtained Marks by Fuzzy LOOKUP Algorithm
In this case, our goal is to use the fuzzy LOOKUP algorithm by using the Fuzzy LOOKUP Add-in feature. The feature is easy to add and use. We can learn this method by following the below steps.
Steps:
- First, we have two tables. In the first table, we have Name in column B and Science in Column C. But in the second table, we have Name in column E and Math in Column F. We will use this dataset to explain the whole method.
- Next, if you don’t have Fuzzy LOOKUP built-in in Excel, then you have to download it from Microsoft Excel Note that we are using Microsoft 365 in this article.
- After that, you have to install the downloaded file and if you restart the Excel file again, you will get the Fuzzy LOOKUP Add-in in the Excel tab.
- Subsequently, you will get the first data table accordingly.
- Similarly, repeat the same process to get the second data table similar to the below image.
- Afterward, click on the table and go to the Table Design tab, and in the Table Name option set the name of the table accordingly. In this first table, we have set the name of the table as ScienceMarks.
- Moreover, if you want to select a different name for the table, then you have to again select the second table and go to the Table Design tab, and in the Table Name option set the name of the table accordingly. In this first table, we have set the name of the table as MathMarks. Note that, you have to name the table again individually.
- Furthermore, select the two tables and click on the Fuzzy Lookup feature.
- After that, in the Fuzzy Lookup feature, select the Left Table and Right Table accordingly. In this case, we have set the ScienceMarks table as the Left table and the MathMarks table as the Right table.
- Then, tick the cells in the output columns option and press GO.
- Finally, you will get a new table where you will find the similarity of the two tables in the same worksheet.
Hence, we learned how to use the fuzzy LOOKUP algorithm by using the Fuzzy LOOKUP Add-in feature.
2. Merging Official Data by Fuzzy LOOKUP Algorithm
For this instance, our goal is to use the fuzzy LOOKUP algorithm by using the fuzzy match (Power Query) in Excel. In this case, we will try to use different data tables and merge them to find the fuzzy match in Excel. We can easily learn this whole process by following the below steps.
Steps:
- At first, we have a total of three data tables. The first table consists of Employee and Department, in the second table, we have Employee and Joining Date and in the third table, we have Full Names and Short Names. We will use this dataset to explain the whole process.
- In addition, convert the data ranges into data tables by following the steps of the first method.
- Hence, select the first data table >> Data >> From Table/Range Note, for further use, you have to change the headings of one table to From and To. In this case, we have changed the heading of the last table headings into From and To.
- Therefore, in the Power Query Editor, you will get the result, and then in the Close and Load option select the Close and Load to option.
- Furthermore, the Import Data window will open on the screen. In this window, select the Only Create Connection option and press OK.
- Afterward, on the right side of the window, you will find the new Queries.
- Next, repeat the same process again to connect the whole tables in this section.
- Then, go to Data >> Get Data >> Combine Queries >> Merge options.
- Moreover, in the Merge window, select the first two tables accordingly. You have to select the first column of the table manually in the section. Then tick the Use fuzzy matching to perform option and click on the triangle named Fuzzy matching feature.
- Subsequently, in the Fuzzy matching options, you have to select the third table in the Transformation Table option and press OK.
- Therefore, you will get the result but the Joining Date column is empty initially.
- Hence, you have to click on the arrow sign and select only the Joining Date option. Then, press OK.
- Now, you will get the result in the Merge1 section, and press Close and Load to get the result.
- Last, you will get the final result accordingly.
Therefore, we have learned how to use the fuzzy LOOKUP algorithm by using the fuzzy match ( Power Query) in Excel.
Read More: How to Perform Employee Scheduling Algorithm in Excel
Conclusion
Henceforth, follow the above-described methods. Hopefully, these methods will help you to learn how to use the fuzzy LOOKUP algorithm in Excel. We will be glad to know if you can execute the task in any other way. Follow the ExcelDemy website for more articles like this. Please feel free to add comments, suggestions, or questions in the section below if you have any confusion or face any problems. We will try our best to solve the problem or work with your suggestions.
