Manually reconciling inconsistent text strings in Excel is a notoriously tedious and error-prone struggle for data analysts. While standard funding sources typically prioritize enterprise-level master data management software, departments often lack the budget for these platforms. Fortunately, leveraging Excel for fuzzy matching grants teams immediate analytical capability without additional software costs. The critical stipulation is that native Excel formulas like VLOOKUP only handle exact matches, necessitating the Microsoft Fuzzy Lookup Add-In for true similarity scoring. Below, we explore how to install this tool, build comparison formulas, and interpret similarity thresholds to streamline your data cleansing workflow.

Excel Formula to Compare Two Strings for Fuzzy Text Match

Data cleaning and preparation are some of the most common challenges in Excel. One of the most frustrating hurdles is dealing with inconsistent text data. Whether you are merging customer lists, reconciling supplier invoices, or comparing product catalogs, you will inevitably encounter variations like "Apple Inc." versus "Apple," or "John Smith" versus "Jon Smith."

Standard lookup functions like VLOOKUP, XLOOKUP, or INDEX/MATCH are binary: they require an exact match (or an alphabetical approximation that rarely works for messy text). To solve this, you need fuzzy text matching-the ability to compare two strings and determine how similar they are on a scale from 0% to 100%.

This comprehensive guide explores four powerful ways to perform fuzzy text matching in Excel, ranging from native formulas and Power Query to advanced VBA macros and official add-ins.

Method 1: Native Excel Formula for Basic Character Overlap

If you cannot use VBA or Power Query due to organizational security policies, you can use a creative native formula to calculate a basic similarity score. This formula counts how many characters from the first string exist inside the second string, giving you a rough percentage of overlap.

The Character-Overlap Formula

Assuming your first string is in cell A2 and the second string is in cell B2, enter the following array formula (press Ctrl + Shift + Enter in older Excel versions, or just Enter in Excel 365):

=SUMPRODUCT(--(ISNUMBER(SEARCH(MID(A2, ROW(INDIRECT("1:" & LEN(A2))), 1), B2)))) / LEN(A2)

How It Works

1. MID(A2, ROW(INDIRECT("1:" & LEN(A2))), 1): This dissects the string in cell A2 into an array of individual characters.
2. SEARCH(..., B2): It searches for each of those individual characters inside cell B2. If a character is found, it returns its position (a number); if not, it returns a #VALUE! error.
3. ISNUMBER(...): This converts the numbers to TRUE and the errors to FALSE.
4. --(...): The double unary operator (double minus) converts TRUE/FALSE values into 1s and 0s.
5. SUMPRODUCT(...): This sums all the 1s, which represents the total number of characters from A2 found in B2.
6. / LEN(A2): Finally, dividing by the total length of A2 converts the count into a percentage score.

Limitation: This method does not account for character order. For example, "cat" and "act" would return a 100% match because they share the exact same characters, even though they are different words.

Method 2: Power Query Fuzzy Merge (No-Code & Highly Robust)

For large datasets, the built-in Fuzzy Match engine in Power Query (available in Excel 2016 and newer) is the most professional and scalable approach. It uses the Jaccard similarity index behind the scenes to compare sets of characters.

Step-by-Step Guide:

1. Select your first table, go to the Data tab, and click From Table/Range to load it into Power Query. Do the same for your second table.
2. In the Power Query editor, go to Home > Merge Queries (or Merge Queries as New).
3. In the Merge window, select the column from Table 1 and the matching column from Table 2.
4. Check the box that says "Use fuzzy matching to perform the merge".
5. Expand the Fuzzy merge options to customize your matching settings:
   • Similarity threshold: Set between 0.00 (completely different) and 1.00 (exact match). The default is 0.80, which is usually the sweet spot.
   • Ignore case: Keeps "APPLE" and "apple" as matches.
   • Match by combining text parts: Matches strings like "Micro Soft" with "Microsoft".
   • Transformation table: An optional table where you map known synonyms (e.g., mapping "MSFT" to "Microsoft").
6. Click OK, expand the merged column, and load the clean data back to Excel.

Method 3: VBA User Defined Function (Levenshtein Distance)

The gold standard for programmatic fuzzy matching is the Levenshtein Distance algorithm. This algorithm counts the minimum number of single-character edits (insertions, deletions, or substitutions) required to change one word into another.

By implementing this in VBA, you can create a custom Excel function called =FuzzyMatchPercent(string1, string2).

The VBA Code

To add this code, press Alt + F11 to open the VBA Editor, click Insert > Module, and paste the following code:

Function LevenshteinDistance(ByVal s As String, ByVal t As String) As Long
    Dim i As Long, j As Long, cost As Long
    Dim lenS As Long, lenT As Long
    Dim arr() As Long
    
    s = LCase(Trim(s))
    t = LCase(Trim(t))
    
    lenS = Len(s)
    lenT = Len(t)
    
    If lenS = 0 Then LevenshteinDistance = lenT: Exit Function
    If lenT = 0 Then LevenshteinDistance = lenS: Exit Function
    
    ReDim arr(0 To lenS, 0 To lenT)
    
    For i = 0 To lenS: arr(i, 0) = i: Next i
    For j = 0 To lenT: arr(0, j) = j: Next j
    
    For i = 1 To lenS
        For j = 1 To lenT
            If Mid(s, i, 1) = Mid(t, j, 1) Then
                cost = 0
            Else
                cost = 1
            End If
            
            arr(i, j) = Application.Min(arr(i - 1, j) + 1, _
                                        arr(i, j - 1) + 1, _
                                        arr(i - 1, j - 1) + cost)
        Next j
    Next i
    
    LevenshteinDistance = arr(lenS, lenT)
End Function
Function FuzzyMatchPercent(ByVal string1 As String, ByVal string2 As String) As Double
    Dim maxLen As Long
    Dim distance As Long
    
    string1 = Trim(string1)
    string2 = Trim(string2)
    
    If string1 = "" Or string2 = "" Then
        FuzzyMatchPercent = 0
        Exit Function
    End If
    
    maxLen = Application.Max(Len(string1), Len(string2))
    distance = LevenshteinDistance(string1, string2)
    
    FuzzyMatchPercent = 1 - (distance / maxLen)
End Function

How to Use It in Your Worksheet

Once the code is saved, return to your Excel worksheet. You can now use your custom function just like any standard Excel formula:

=FuzzyMatchPercent(A2, B2)

Format the output cell as a Percentage. If A2 contains "Microsoft Corp" and B2 contains "Microsoft Corporation", the formula will return a highly accurate similarity ratio (approximately 71%).

Method 4: Microsoft Fuzzy Lookup Add-In for Excel

For legacy Excel users or those looking for a dedicated out-of-the-box analytical tool, Microsoft provides a free Fuzzy Lookup Add-In.

How to Download and Use:

1. Download the "Fuzzy Lookup Add-In for Excel" from the official Microsoft Download Center.
2. After installation, a new Fuzzy Lookup tab will appear in your Excel ribbon.
3. Convert both of your data sets into Excel Tables (Ctrl + T).
4. Click the Fuzzy Lookup button in the ribbon. A sidebar panel will appear on the right side of your screen.
5. Select your Left Table and Right Table, and select the columns you want to join. Click the join button (the relationship symbol) between them.
6. Configure your output columns. Crucially, check the box for FuzzyLookup.Similarity to output the confidence score alongside your matched columns.
7. Select an empty cell in your workbook and click Go to run the matching model.

Best Practices for Fuzzy Text Matching in Excel

No mathematical algorithm is perfectly psychic. To get the best results, always preprocess your text before running any fuzzy match formula or tool:

Summary: Which Method Should You Choose?

• Use Power Query (Method 2) if you are working with large databases and tables, need to build a repeatable automation workflow, or need a robust no-code solution.
• Use VBA (Method 3) if you want real-time interactive calculations directly inside individual spreadsheet cells as you edit data.
• Use the Native Formula (Method 1) if you have strict workbook sharing restrictions that forbid macros (.xlsm formats) or external add-ins.