August 19, 2020 - by Bill Jelen
When you use VLOOKUP, HLOOKUP, or INDEX/MATCH, Excel is expecting an exact match. But in real life, data is messy. Several years ago, the research team at Microsoft Labs released a free Fuzzy Lookup add-in. The functionality was never added to Excel, but later showed up in SQL Server. However, the free tool is still available from //mrx.cl/fuzzylookup
Download and install the add-in. The last step of the install process lets you open the install folder where you will you will find a ReadMe document and a sample Excel file.
Open the sample file. On the Fuzzy Lookup tab, choose Fuzzy Lookup. In the panel that opens, choose the Left Table, the Right Table, and the columns in common.
Optionally, choose that you want to see the best 2 or best N matches. Although it is more work, I always ask for at least two matches because Fuzzy Matches are never perfect.
Here are the results. Note if you had asked for only 1 match, you would not see the choice between Coca-Cola Company and Coca-Cola Enterprises. Also - beware of Fuzzy Match algorithms: ATT Corp and ITT Corporation are very similar and could be reported as a match. Someone should always review the matches to determine if they are accurate.
Title Photo: Dayne Topkin at Unsplash.com
This article is an excerpt from MrExcel 2020 - Seeing Excel Clearly.
MrExcel 2022 Boosting Excel
The 6th Edition of MrExcel XL, updated with new functions released for Microsoft 365.
Today we are going to learn how to use VLOOKUP to search for Fuzzy Match in Excel.
While working with larger data sets, we often try to filter out similar values. One of these matching types is called Fuzzy Match, where the values are not exactly the same, but they are still matched based on their similarity.
So. Let’s discuss how you can use the VBA VLOOKUP function of Excel to search for Fuzzy Match.
Introduction to Fuzzy Match
A Fuzzy Match is a type of partial match.
In these types of matches, one text does not match fully to the other text. But the important sections of the text do match the other text.
In the given example, the book “The History of India during the World War” contains three important sections: History, India, and World War.
Therefore all the books that contain either one or more than one of these sections will fuzzily match the book.
So, the fuzzy matches are:
- The History of the Second World War
- History of the Ancient Greece
- World War: Causes and Effects
- The Indus Civilization: An Ancient History
- India Wins Freedom
- Adolf Hitler: Before and After the World War
- The Discovery of India
Download Practice Workbook
3 Approaches for VLOOKUP Fuzzy Match in Excel
Here we’ve got a data set with the Names of some books of a bookshop called.
Our objective today is to use the VLOOKUP function of Excel to generate some Fuzzy Matches. Let’s discuss on 3 different approaches.
1. VLOOKUP Fuzzy Match Using Wildcards (Entire Lookup_Value Matching)
- First of all, we shall generate some fuzzy matches using the wildcard character the Asterisk (*) symbol. But remember, you have to match the entire lookup_value in this method, not the separate parts of the lookup_value.
For example, we can find a book containing the text “Second World War” in this way.
Only the books having the full text “Second World War” will match.
The formula is simple. Place an Asterisk (*) symbol on both ends of the lookup_value text.
The formula will be:
=VLOOKUP("*Second World War*",B5:B22,1,FALSE)
- You can also use a cell reference in place of the original text too. Use the Ampersand (&) symbol to merge them into a single text. Like this:
=VLOOKUP("*"&D5&"*",B5:B22,1,FALSE)
To know more about VLOOKUP using wildcards, visit this article.
Read More: How to Perform VLOOKUP with Wildcard in Excel (2 Methods)
2. Fuzzy Match Using VBA
The method in the previous section fulfills our purpose partially, but not to the fullest.
Now we will derive a formula using a VBA code that will fulfill our purpose almost completely.
- At first, open a VBA window and insert the following VBA code in a new module:
Code:
Function FUZZYMATCH(str As String, rng As Range) str = LCase(str) Dim Remove_1(5) As Variant Remove_1(0) = "," Remove_1(1) = "." Remove_1(2) = ":" Remove_1(3) = "-" Remove_1(4) = ";" Remove_1(5) = "?" Dim Rem_Str_1 As String Rem_Str_1 = str Dim rem_count_1 As Variant For Each rem_count_1 In Remove_1 Rem_Str_1 = Replace(Rem_Str_1, rem_count_1, "") Next rem_count_1 Words = Split(Rem_Str_1) Dim i As Variant For i = 0 To UBound(Words) If Len(Words(i)) = 1 Or Len(Words(i)) = 2 Then Words(i) = Replace(Words(i), Words(i), " bt ") End If Next i Dim Final_Remove(26) As Variant Final_Remove(0) = "the" Final_Remove(1) = "and" Final_Remove(2) = "but" Final_Remove(3) = "with" Final_Remove(4) = "into" Final_Remove(5) = "before" Final_Remove(6) = "after" Final_Remove(7) = "beyond" Final_Remove(8) = "here" Final_Remove(9) = "there" Final_Remove(10) = "his" Final_Remove(11) = "her" Final_Remove(12) = "him" Final_Remove(13) = "can" Final_Remove(14) = "could" Final_Remove(15) = "may" Final_Remove(16) = "might" Final_Remove(17) = "shall" Final_Remove(18) = "should" Final_Remove(19) = "will" Final_Remove(20) = "would" Final_Remove(21) = "this" Final_Remove(22) = "that" Final_Remove(23) = "have" Final_Remove(24) = "has" Final_Remove(25) = "had" Final_Remove(26) = "during" Dim w As Variant Dim ww As Variant For w = 0 To UBound(Words) For Each ww In Final_Remove If Words(w) = ww Then Words(w) = Replace(Words(w), Words(w), " bt ") Exit For ' End If Next ww Next w Dim Lookup As Variant Dim x As Integer x = rng.Rows.count ReDim Lookup(x - 1) Dim j As Variant j = 0 Dim k As Variant For Each k In rng Lookup(j) = k j = j + 1 Next k Dim Lower As Variant ReDim Lower(UBound(Lookup)) Dim u As Variant For u = 0 To UBound(Lookup) Lower(u) = LCase(Lookup(u)) Next u Dim out As Variant ReDim out(UBound(Lookup), 0) Dim count As Integer co = 0 mark = 0 Dim m As Variant For m = 0 To UBound(Lower) Dim n As Variant For Each n In Words Dim o As Variant For o = 1 To Len(Lower(m)) If Mid(Lower(m), o, Len(n)) = n Then out(co, 0) = Lookup(m) co = co + 1 mark = mark + 1 Exit For End If Next o If mark > 0 Then Exit For End If Next n mark = 0 Next m Dim output As Variant ReDim output(co - 1, 0) Dim z As Variant For z = 0 To co - 1 output(z, 0) = out(z, 0) Next z FUZZYMATCH = output End FunctionThis code builds a function called FUZZYMATCH.
- Now, save it following the steps of Method 3 of this article.
This FUZZYMATCH function finds out all the Fuzzy Matches of a Lookup Value directly.
The Syntax of this FUZZYMATCH function is:
=FUZZYMATCH(lookup_value,lookup_range)
To find out the Fuzzy Matches of the book “The History of India during the World War”, enter this lookup_value in a cell (D5 in this example) and enter this formula in another cell:
See, we have found out all the Fuzzy Matches of the book “The History of India during the World War”
- Here D5 is the cell reference of the lookup_value (“The History of India during the World War”).
- B5:B22 is the lookup_range.
Let’s find out the Fuzzy Matches of another book called “A Notebook of the Causes behind the Crime of Big Cities”.
Enter this lookup_value in a cell (D5 in this example) and enter this formula in another cell:
💡 Explanation of Formula
- The FUZZYMATCH function is the function we built in VBA. It takes a string called lookup_value and a range of cells called lookup_range and returns an array of all the Fuzzy Matches of the string.
- Therefore FUZZYMATCH(D5,B5:B22) returns an array of all the Fuzzy Matches of the string in cell D5 from the range B5:B22.
Read More: How to VLOOKUP Partial Text in Excel (With Alternatives)
Similar Readings
- VLOOKUP Not Working (8 Reasons & Solutions)
- INDEX MATCH vs VLOOKUP Function (9 Examples)
- Use VLOOKUP with Multiple Criteria in Excel (6 Methods + Alternatives)
- Excel VLOOKUP to Return Multiple Values Vertically
- VLOOKUP and Return All Matches in Excel (7 Ways)
3. Fuzzy Match Using Fuzzy Lookup Add-in of Excel
Microsoft Excel provides an Add-in called Fuzzy Lookup. Using it, you can match two tables for Fuzzy Lookup.
- At first, download and install the Add-in from this link.
- After successfully downloading and installing it, you will find the Fuzzy Lookup Add-in your Excel Toolbar.
- Then arrange the data sets into two tables that you want to match.
- Here I’ve got two tables containing two lists of books from two bookshops called Robert Bookshop and Martin Bookshop.
- Next, go to the Fuzzy Lookup tab> click Fuzzy Lookup tool in Excel Toolbar.
- Hence, you will get a Fuzzy Lookup table created in the side panel of your workbook.
In the Left Table and Right Table options, choose the names of the two tables.
For the sake of this example, choose Robert and Martin.
Then in the Columns section, choose the names of the columns of each table.
In the Match Column section, select the type of match that you want between the two columns. For Fuzzy Match, select Default.
- Finally, click on Go. You will get the matching ratio of the tables in a new table.
Read More: VLOOKUP To Compare Two Lists in Excel (2 or More Ways)
Conclusion
Using these methods, you can use the VLOOKUP function of Excel to search for Fuzzy Match. Though these methods are not 100% efficient, still they are very useful. Do you have any questions? Feel free to ask them in the comment box. Don’t forget to share if you got better methods. Stay connected with ExcelDemy.
Further Readings
- How to Use VLOOKUP for Rows in Excel (With Alternatives)
- VLOOKUP with Multiple Matches in Excel
- How to VLOOKUP from Multiple Columns with Only One Return in Excel (2 Ways)
- VLOOKUP to Search Text in Excel (4 Easy Ways)
- IF and VLOOKUP Nested Function (7 Ways)
- VLOOKUP Partial Text from a Single Cell in Excel
- Excel LOOKUP vs VLOOKUP: With 3 Examples