Want to learn how to perform Fuzzy Matching with VLOOKUP in Excel? We’ve got you covered.
Sometimes, we want to find things that are kind of similar but not exactly the same. This is what we call a “Fuzzy Match.” When working with large datasets, finding similar values becomes crucial. Fuzzy Match allows us to locate items not precisely identical but similar. In this tutorial, we’ll explore how VLOOKUP, our trusty Excel tool, can be employed to perform Fuzzy Matching.
So, without wasting another second, let’s dive in.
How to perform Fuzzy Matching with VLOOKUP in Excel
VLOOKUP is like a detective that helps us search for things in our data. We can use it to look for similar items, not just the exact ones. So, when we want to find things that are a bit alike, we can use VLOOKUP to do a Fuzzy Match.
In our dataset, the target for fuzzy matching is “Microsoft Corp.” There are two key sections: “Microsoft” and “Corp.” We seek to identify all entries in our dataset that could potentially refer to Microsoft Corporation, even if the names are not spelled or formatted exactly the same. The aim is to capture all variations that share significant similarities with our target name.
Here’s a breakdown of the fuzzy matches for “Microsoft Corp.” from the dataset:
- “MS” is a common abbreviation for “Microsoft,” and “Corp.” is a standard abbreviation for “Corporation.”
- Microsoft Corporation is a fuzzy match for “Microsoft Corp.” because it contains the full word “Microsoft,” which is the most significant part of the name, and “Corporation,” which is a formal extension of “Corp.” Hence, despite the difference in the length of the name, it is clear that both entries refer to the same entity.
- Micro-soft Corp. is identified as a fuzzy match due to the presence of a hyphen in “Micro-soft,” which is a minor variation from “Microsoft.” It also ends with “Corp.,” matching the abbreviation in the original name.
We will use Excel’s VLOOKUP to find things that are kind of similar, not exactly the same. We’ll learn three different ways to do this.
Method 1 – VLOOKUP Fuzzy Match using wildcards (entire lookup_value matching)
Let’s go through the process step by step of using VLOOKUP with wildcards to perform a fuzzy match in Excel. This method will find cells that contain the entire lookup value within a larger string.
- Ensure your dataset is organized such that the first column of your range (where VLOOKUP will search) contains the text you want to search within.
- Open Excel and your Workbook.
- Navigate to the worksheet with your data.
- Now, insert the VLOOKUP formula. For this, click on the cell where you want to display the result of the VLOOKUP. This could be next to your dataset or in a separate table where you’re summarising data.
=VLOOKUP(“*” & lookup_value & “*”, search_range, column_index, TRUE)
In this formula:
- lookup_value is the text you’re searching for, e.g., “Microsoft”.
- Search_range is the range where the search will take place, e.g., A2:B10. Remember, VLOOKUP will search in the first column of this range.
- Column_index is the column number in the range from which to retrieve the value. If you want to return a value from column A, this would be 1.
- TRUE for range_lookup allows approximate matching, which is necessary when using wildcards.
Now let’s consider our example dataset:
- Let’s say you have company names in column A and their corresponding data in column B.
- For this example, we’ll use “Microsoft” as the fuzzy match term.
We have used the cell reference in the LOOKUP value you can also use the lookup value text which might look like this in our case
=VLOOKUP("*Microsoft*", A2:A11, 1, FALSE) Note: While using wildcards like the asterisk (*) in Excel provides flexibility for finding partial matches, be cautious as they might bring back results you didn’t expect, making it necessary to use them carefully.
Method 2 – Fuzzy Match using VBA
Performing fuzzy matching using VBA (Visual Basic for Applications) in Excel allows for a more customized and flexible approach. Creating a fuzzy match function in VBA for Excel involves writing a custom function to compare the similarity between strings. The most common method for fuzzy matching in VBA is to use pattern-matching techniques.
Here are the general steps you’d follow to create a simple fuzzy match using VBA:
- In Excel, press Alt + F11 to open the VBA editor.
- In the VBA editor, right-click on any existing sheet in the ‘Project’ window and select Insert > Module. This will create a new module where you can write your code.
- Within the new module, you can write a function that calculates the similarity between two strings.
Now let’s write a function for our example where we have to search all the approximate matches for MS corporation:
This VBA code creates a function named FuzzyMatchLookup which is used to determine all fuzzy matches for a given lookup value within a specified range.
The syntax for the FuzzyMatchLookup function is as follows:
=FuzzyMatchLookup(lookup_value, lookup_range)
This can be particularly useful for matching text strings where there may be minor variations in spelling, spacing, or punctuation.
Here’s a brief explanation of how you use the FuzzyMatchLookup function:
- lookup_value: This is the value you’re searching for. It could be a name, title, or any text string.
- lookup_range: This is the range of cells in which you want to search for the lookup_value. For instance, if you have a list of company names in cells A2 through A11, A2:A11 would be your lookup_range.
So, as per our example, our FuzzyMatchLookup function will be as follows:
=FuzzyMatchLookup(D3, A2:A11)
When you enter this formula into a cell (for example, in cell E3), it will search for fuzzy matches to “MS Corporation” within the range A2:A11, which contains a list of company names.
Method 3 – Using Fuzzy Lookup add-in of Excel
Microsoft Excel offers a handy tool called Fuzzy Lookup Add-in to match tables with a fuzzy logic approach.
Begin by downloading and installing the add-in. Once installed, you’ll spot the Fuzzy Lookup Add-in in your Excel Toolbar, ready to enhance your data-matching capabilities.
- Organize the datasets into two tables that you aim to compare and find matches between.
- I’ve got two tables containing lists of famous places from Agency A and Famous places from Agency B.
- Next, go to the Fuzzy Lookup tab> click Fuzzy Lookup tool in the Excel Toolbar.
- As a result, a Fuzzy Lookup table will be generated in the side panel of your workbook.
- In the Left Table and Right Table options, designate the names of the two tables. For this illustration, Agency A and Agency B.
- Next, in the Columns section, specify the names of the columns for each table.
- Then, in the Match Column section, choose the type of match you desire between the two columns. Opt for the Default option for a Fuzzy Match.
- Conclude the process by clicking on “Go.” A new table will display the matching ratio between the two tables.
Conclusion
In summary, these techniques empower you to employ Excel’s VLOOKUP function for Fuzzy Match searches. While they may not guarantee 100% accuracy, they remain highly beneficial, especially when you’re dealing with large data sheets.
Learn more about Excel and its functions with these helpful guides:
