How to Vlookup similar (but not matching) records ?

Rare but a very relevant problem! Isn’t it? A quick heads up on how does it arise?

Imagine for a moment that the ERP at Peter Brothers is not very robust, every time a customer comes in (even if the same customer) the MIS guy makes a New Record.

His whims led to too many similar company names and now it is impossible for Vlookup to match similar company names.

I was asked this question by a participant in a training session about 2 years back, knowing that there was no straight answer to that I kept thinking of a Macro (VBA coding) based solution, a few days later to my surprise I found that Microsoft had already thought about it and rendered a beautiful solution. I am writing about it today (better late than never 😛 )

Without reinventing the wheel let’s see what Microsoft has to offer? Ready for it?

Here is the Data

Here is a list of 70+ records with similar but not matching company names. Notice that for each similar record a separate customer ID has been generated.

Vlookup similar but not maching records 4

and we have another set of refined company names (without any similar duplication). This is mandatory to have

Vlookup similar but not maching records 5

Download this data. Our objective is to delete the similar company names and retain just one with a unique Customer ID

Here Comes Fuzzy Lookup by Microsoft

This page has everything you want to know about how to install this add in your Excel

It has the download link
And instructions and other details that you may need along the way

Fuzzy Lookup Download Link. Take a moment to install Fuzzy Lookup Add In

This is how it appears in Excel after installation

Vlookup similar but not maching records 3

Now convert your data into Tables

Use the Shortcut Ctrl + T to covert both the data sets into a table
Give your tables a suitable name from the Design Tab (appears when you click on any cell inside the table)

Vlookup similar but not maching records 6

Let’s Start Fuzzy Lookup

Click on Fuzzy Lookup. It will display a settings pop up

Vlookup similar but not maching records 7

Follow the steps

Vlookup similar but not maching records 8

Select the Right Settings
Click on the Relationship Button between ‘Left Column’ & ‘Right Column’
Tick the items you want in the Output Columns
Set the Similarity Threshold
1. The higher the threshold, the more strictly will excel match the records
2. The lesser the threshold, the more loosely will excel match the records
Before clicking on Go. Choose the cell where you want to place the new table. Click on GO
Done!

Let’s see the Results

Vlookup similar but not maching records 9

These are the similar customer names
Customer ID has been pulled as it is from the ‘SimilarName’ Table
Customer Name (not similar ones) have been added in front of each similar customer name
- The empty records could not be matched
- May be reducing the similarity threshold can help
- This is what we wanted. Now we can easily filter out the similar ones
Similarity Score – The higher the score, the higher the chance that excel has given the correct match

How did you find Fuzzy Lookup?

Put down your comments below and also share if you have solved a complex problem with a simple add in