How To Use Fuzzy Lookup

Today I will be showing how to use VLOOKUP to search for Fuzzy Match in Excel.

While working with larger data sets, many times we try to filter out like types of values. Ane of these types of matching is chosen Fuzzy Lucifer, where the values are not exactly the same, merely notwithstanding, they are matched based on their similarity.

Read more: How to Use VLOOKUP for Partial Match in Excel

Today I volition be showing how you lot can utilize the VLOOKUP role of Excel to search for Fuzzy Match.

An Introduction to Fuzzy Lucifer

Fuzzy Match Introduction

A Fuzzy Match is a type of partial match.

In these types of matches, one text does non match fully to the other text. But the important sections of the text do friction 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 Earth War.

Therefore all the books that comprise 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
Earth War: Causes and Effects
The Indus Civilization: An Ancient History
India Wins Liberty
Adolf Hitler: Before and Afterward the Earth War
The Discovery of India

Download Exercise Workbook

VLOOKUP Fuzzy Lucifer in Excel

Here we've got a data set with the Names of some books of a bookshop called Kingfisher Bookstore.

Data Set for VLOOKUP Fuzzy Match

Our objective today is to apply the VLOOKUP part of Excel to generate some Fuzzy Matches.

ane. VLOOKUP Fuzzy Match Using Wildcards (Matching the Unabridged Lookup_Value)

First of all, we shall generate some fuzzy matches using the wildcard character the Asterisk (*) symbol.

Read more: How to Perform VLOOKUP with Wildcard in Excel

But retrieve, you lot take to lucifer the entire lookup_value in this method, not the split parts of the lookup_value.

For case, we can find a book containing the text "Second World War" in this way.

Simply the books having the full text "2nd World State of war" will friction match.

The formula is simple. Place an Asterisk (*) symbol on both ends of the lookup_value text.

The formula will exist:

=VLOOKUP("*Second World State of war*",B4:B21,one,FALSE)

Asterisk Symbol Formula for VLOOKUP Fuzzy Match

Or you lot tin apply a cell reference in identify of the original text too. Use the Ampersand (&) symbol to merge them into a unmarried text. Similar this:

Asterisk Symbol Formula for VLOOKUP Fuzzy Match

=VLOOKUP("*"&D4&"*",B4:B21,1,FALSE)

To know more near VLOOKUP using wildcards, visit this article.

2. Fuzzy Match Using VBA

The method in the previous section fulfills our purpose partially, simply not to the fullest.

Now we volition derive a formula using a VBA code that volition fulfill our purpose almost completely.

Open up a VBA window and inserts the following VBA lawmaking in a new module:

Code:

          Part FUZZYMATCH(str As String, rng Every bit 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(v) = "?"  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 = Supervene upon(Rem_Str_1, rem_count_1, "") Next rem_count_1  Words = Dissever(Rem_Str_1)  Dim i Every bit Variant For i = 0 To UBound(Words)     If Len(Words(i)) = 1 Or Len(Words(i)) = 2 And 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(ane) = "and" Final_Remove(2) = "but" Final_Remove(3) = "with" Final_Remove(4) = "into" Final_Remove(5) = "before" Final_Remove(6) = "afterwards" Final_Remove(7) = "across" Final_Remove(8) = "here" Final_Remove(9) = "there" Final_Remove(x) = "his" Final_Remove(11) = "her" Final_Remove(12) = "him" Final_Remove(13) = "can" Final_Remove(14) = "could" Final_Remove(xv) = "may" Final_Remove(16) = "might" Final_Remove(17) = "shall" Final_Remove(18) = "should" Final_Remove(xix) = "will" Final_Remove(twenty) = "would" Final_Remove(21) = "this" Final_Remove(22) = "that" Final_Remove(23) = "take" Final_Remove(24) = "has" Final_Remove(25) = "had" Final_Remove(26) = "during"  Dim w As Variant Dim ww Equally Variant For west = 0 To UBound(Words)     For Each ww In Final_Remove         If Words(w) = ww Then             Words(w) = Replace(Words(w), Words(w), " bt ")             Get out For '         End If     Side by side 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 m As Variant For Each k In rng     Lookup(j) = k     j = j + 1 Next k  Dim Lower Equally Variant ReDim Lower(UBound(Lookup)) Dim u As Variant For u = 0 To UBound(Lookup)     Lower(u) = LCase(Lookup(u)) Side by side u  Dim out As Variant ReDim out(UBound(Lookup), 0)  Dim count Equally Integer co = 0 mark = 0 Dim m Equally Variant For grand = 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(g), o, Len(n)) = n Then                 out(co, 0) = Lookup(1000)                 co = co + one                 marker = mark + ane                 Exit For             End If         Side by side o         If mark > 0 Then             Exit For         End If     Next n     mark = 0 Adjacent m  Dim output Equally Variant ReDim output(co - ane, 0) Dim z Every bit Variant For z = 0 To co - 1     output(z, 0) = out(z, 0) Next z  FUZZYMATCH = output                       End Function

VBA Code for VLOOKUP Fuzzy Match

This code builds a function called FUZZYMATCH.

Save it (Visit this commodity to see how to salvage a VBA code in Excel)

This FUZZYMATCH function finds out all the Fuzzy Matches of a Lookup Value straight.

The Syntax of this FUZZYMATCH function is:

=FUZZYMATCH(lookup_value,lookup_range)

To find out the Fuzzy Matches of the volume "The History of India during the Globe War", enter this lookup_value in a cell (D4 in this example) and enter this formula in some other cell:

FUZZYMATCH Function Made with VBA for VLOOKUP Fuzzy Match

See, we have found out all the Fuzzy Matches of the book "The History of Bharat during the World War"

Here D4 is the cell reference of the lookup_value ("The History of India during the Earth War").
B4:B2ane is the lookup_range.

Let'south find out the Fuzzy Matches of another book called "A Notebook of the Causes behind the Criminal offense of Big Cities".

Enter this lookup_value in a jail cell (D4 in this example) and enter this formula in another cell:

FUZZYMATCH Function Made with VBA for VLOOKUP Fuzzy Match

Explanation of the Formula

The FUZZYMATCH role 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 cord.
Therefore FUZZYMATCH(D4,B4:B21) returns an array of all the Fuzzy Matches of the string in cell D4 from the range B4:B21.

three. Fuzzy Friction match Using Excel'southward Fuzzy Lookup Add-in

Microsoft Excel provides an Add-in called Fuzzy Lookup. Using it, yous can match two tables for Fuzzy Lookup.

Download and install the Add-in from this link.

After downloading and installing it successfully, you will find the Fuzzy Lookup Add-in your Excel Toolbar like so.

Fuzzy Lookup Add-in in Excel

Then arrange the data sets into two tables that you want to friction match.

Two Tables for Fuzzy Lookup in Excel

Here I've got two tables containing 2 lists of books from ii bookshops called Robert Bookshop and Martin Bookshop.

Next, go to Fuzzy Lookup>Fuzzy Lookup in Excel Toolbar.

Fuzzy Lookup Option in Excel Toolbar

Click it. Y'all volition become a Fuzzy Lookup table created in the side console of your workbook.

In the Left Table and Right Table options, choose the names of the two tables.

For the sake of this example, cull Robert and Martin.

Then in the Columns department, cull the names of the columns of each table.

In the Lucifer Cavalcade department, select the type of match that yous want between the two columns. For Fuzzy Lucifer, select Default.

Fuzzy Lookup Window in Excel

Finally, click on Get. You will become the matching ratio of the tables in a new table.

Fuzzy Lookup Table Created in Excel

Conclusion

Using these methods, you can use the VLOOKUP function of Excel to search for Fuzzy Match. Though these methods are not 100% efficient, however they are very useful. Do y'all have any questions? Feel free to ask us.