How to use Fuzzy Lookup in Excel to Match Names
Think: Sometimes, we need to find a match in-between tables. In such cases we find the data using functions, like vLookup. This works when entries in both columns in the tables are completely identical. Sometimes that may not be the case. There may be spelling differences or some minor differences in the entries. Because of these, functions like VLookup would not work well.
Excel Vlookup formula – Guidebook
Bored of downloading text heavy / copy-pasted eBooks?
If Yes, you will enjoy this guidebook on ‘Excel Vlookup Formulas’ – VLOOKUP, HLOOKUP, MATCH & INDEX.
Excel now has a downloadable AddIn called Fuzzy Lookup to help out with this issue. Fuzzy Lookup finds rows from one table which are identical to rows in another.
Fuzzy Lookup AddIn is available to download from Microsoft. Once you download, it will look like the picture in the slide above on your Excel.
Let us now look at an example to understand how Fuzzy Lookup works.
Assume that you have a table consisting of Company Names and their respective turnover in a year. You need to find the turnover for some selected companies out of the list. This shown in the second table.
Here you may feel it is easy to go through the list and pick up each item one by one. But usually, the lists are huge and it is not easy to find out such names when the list runs into hundreds of pages!
Let us See How Fuzzy Lookup Comes in Handy in Such Cases.
Step 1: Click on the Tab Fuzzy Lookup
Step 2: Click on Fuzzy Lookup button to get a toolbar on the right like shown above.
Step 3: Now you need to feed tables in the toolbar on the right.
- The two tables you have created need to be converted into Tables in the Excel format.
- For this, select the first table and press Ctrl + T on your keyboard.
- Upon the prompt, press Enter. Repeat the same for the other table. Now we have two tables that are ready to be fed into the Fuzzy toolbar on the right.
15 Excel Data Cleaning Tricks Guidebook
Bored of downloading text heavy / copy-pasted eBooks? If Yes, you will enjoy this guidebook (43 pages)
Step 4: On the Fuzzy Lookup toolbar, select both tables.
Step 5: See the picture above. Select the bigger table first.
Step 6: Next, select the corresponding column headers in both tables.
Step 7: Then create a relationship between them through the button in-between them.
Excel Vlookup formula – Guidebook
Bored of downloading text heavy / copy-pasted eBooks?
If Yes, you will enjoy this guidebook on ‘Excel Vlookup Formulas’ – VLOOKUP, HLOOKUP, MATCH & INDEX.
Assign the threshold value
The next part is important: Assign the threshold value. The threshold value indicates the level of comparison essential to compare the two cells. The more the threshold value, the more exactness in comparison will be there. The less the threshold value, the less will be the exactness in comparison of the two cells.
- If you need higher accuracy in matching, the value should be closer to 1.
- If you need lower accuracy, i.e. you need to match very different words, then the value should be closer to 0.
Note: At threshold value 1 Fuzzy Lookup works the same as vLookup!!
Result: Now, finally, click on the cell where you want the reports to be displayed. Then click on Go from the toolbar.
The results will be as displayed above.
Good Job!
We have learned in this tutorial how to match names in Excel where spelling differ or vary.
Have fun!!!
15 Pivot Tables Tricks for Pros
15 Pivot Table tricks to make your Excel data analysis smarter! 5,600+ downloads.
Most Popular Tricks are #3, #7 & #12