Excel Formulas for Fuzzy Text Matching Based on Similarity Thresholds

Manually reconciling inconsistent text data in Excel is a notoriously tedious struggle for database analysts. Often, teams attempt to align internal records with contact lists from standard funding sources, only to find that traditional exact-match formulas fail due to minor typos. Fortunately, deploying a fuzzy match formula grants you the ability to automate this reconciliation with high precision. To ensure accuracy, the key stipulation is establishing a strict similarity threshold to prevent false positives. For example, successfully mapping "Govt Grant Corp" to "Government Grant Corporation" demonstrates this method's power. Below, we outline the exact formula architecture and configuration steps to implement this solution.

Data normalization is one of the most common challenges database administrators, financial analysts, and market researchers face in Excel. Standard lookup functions like VLOOKUP, XLOOKUP, and INDEX/MATCH are exceptional at finding exact matches. However, they fail instantly when faced with minor typos, variations in spacing, missing words, or different suffix styles (e.g., "Apple Inc." vs. "Apple Incorporated").

To solve this, we must turn to fuzzy text matching. Fuzzy matching calculates the degree of similarity between two text strings and returns a score, typically between 0 (completely different) and 1 (an exact match). By setting a similarity threshold (such as 0.80 or 80%), you can instruct Excel to match records that are "close enough."

In this guide, we will explore the three most effective ways to perform fuzzy matching in Excel based on a similarity threshold: using Power Query, writing a custom VBA User-Defined Function (UDF), and leveraging modern Excel 365 formulas.

Method 1: Power Query Fuzzy Merge (The Built-In Modern Solution)

If you are using Excel 2019, Excel 2021, or Microsoft 365, Power Query is the most robust and user-friendly tool for fuzzy matching. It has a built-in fuzzy matching engine that allows you to configure similarity thresholds without writing complex code.

Step-by-Step Implementation:

1. Prepare your tables: Convert both your source data table and lookup table into official Excel Tables (select your data and press Ctrl + T). Name them SourceData and LookupTable.
2. Load tables into Power Query: Select your first table, go to the Data tab, and click From Sheet (or From Table/Range). Close and load it as a connection only. Repeat this process for the second table.
3. Merge Queries: Go to Data > Get Data > Combine Queries > Merge.
4. Configure the Merge window:
   • Select your SourceData table in the top dropdown and your LookupTable in the bottom dropdown.
   • Click on the respective columns in both previews that contain the text you want to match.
   • Check the box labeled "Use fuzzy matching to perform the merge".
5. Adjust the Similarity Threshold:
   • Expand the Fuzzy merge options drop-down menu.
   • In the Similarity threshold box, enter a decimal value between 0.00 and 1.00. The default is 0.80 (80% similarity). If you want looser matching (catching more typos), drop this to 0.70. For stricter matching, raise it to 0.90.
   • Check Ignore case and Match by combining parts of text to increase accuracy.
6. Load the data back: Click OK, expand the newly merged column to retrieve your matched data, and click Close & Load to return the clean matches to your Excel worksheet.

Method 2: VBA User-Defined Function (Normalized Levenshtein Distance)

If your project demands a dynamic, formula-based layout that automatically updates when sheet values change-and you cannot use Power Query-a custom VBA function is the best approach. We will implement the Levenshtein Distance algorithm, which counts the minimum number of single-character edits (insertions, deletions, or substitutions) required to change one word into another.

By normalizing this distance against the length of the longer string, we get a similarity percentage between 0 and 1.

Step 1: Insert the VBA Code

Press Alt + F11 to open the VBA Editor. Click Insert > Module, and paste the following code:

Function LevenshteinSimilarity(ByVal string1 As String, ByVal string2 As String) As Double
    Dim len1 As Long, len2 As Long
    Dim matrix() As Long
    Dim i As Long, j As Long
    Dim cost As Long
    Dim maxLen As Long
    
    ' Clean and normalize text to lowercase
    string1 = LTrim(RTrim(LCase(string1)))
    string2 = LTrim(RTrim(LCase(string2)))
    
    len1 = Len(string1)
    len2 = Len(string2)
    
    If len1 = 0 Then
        If len2 = 0 Then LevenshteinSimilarity = 1.0 Else LevenshteinSimilarity = 0.0
        Exit Function
    End If
    If len2 = 0 Then
        LevenshteinSimilarity = 0.0
        Exit Function
    End If
    
    ' Determine max length for similarity scaling
    If len1 > len2 Then maxLen = len1 Else maxLen = len2
    
    ReDim matrix(0 To len1, 0 To len2)
    
    For i = 0 To len1: matrix(i, 0) = i: Next i
    For j = 0 To len2: matrix(0, j) = j: Next j
    
    For i = 1 To len1
        For j = 1 To len2
            If Mid(string1, i, 1) = Mid(string2, j, 1) Then
                cost = 0
            Else
                cost = 1
            End If
            
            matrix(i, j) = Application.WorksheetFunction.Min( _
                matrix(i - 1, j) + 1, _
                matrix(i, j - 1) + 1, _
                matrix(i - 1, j - 1) + cost)
        Next j
    Next i
    
    ' Calculate similarity percentage
    LevenshteinSimilarity = 1 - (matrix(len1, len2) / maxLen)
End Function

Step 2: Use the Formula in Excel

Once the VBA code is in place, you can use the function =LevenshteinSimilarity(text1, text2) directly inside your cells.

To perform a lookup across an entire table and pull the match only if it meets a similarity threshold of 80% (0.80), combine this custom function with XLOOKUP or INDEX/MATCH. For example, if your search term is in cell A2 and your target lookup column is $E$2:$E$100, enter the following array formula (press Ctrl + Shift + Enter in older Excel versions):

=INDEX($E$2:$G$100, MATCH(MAX(MAP($E$2:$E$100, LAMBDA(val, LevenshteinSimilarity(A2, val)))), MAP($E$2:$E$100, LAMBDA(val, LevenshteinSimilarity(A2, val))), 0), 2)

To make this cleaner, you can separate the similarity scores into a helper column. Put =LevenshteinSimilarity(A2, E2) in a column next to your lookup table, filter/evaluate matches that score >= 0.80, and retrieve corresponding values.

Method 3: Native Excel 365 Token-Overlap Formula (No VBA/No Power Query)

For Microsoft 365 users who want a pure, formulas-only approach without macro workbooks (.xlsm), we can create a token-overlap similarity formula. This method splits both strings into individual words (tokens) and calculates how many words from the search string exist in the target string relative to the total word count.

Assuming cell A2 contains "Microsoft Corporation" and cell B2 contains "Microsoft Corp", the following dynamic formula calculates their token similarity score:

=LET(
    wordsA, TEXTSPLIT(LOWER(A2), " "),
    wordsB, TEXTSPLIT(LOWER(B2), " "),
    commonWords, REDUCE(0, wordsA, LAMBDA(acc,val, acc + IF(ISNUMBER(XMATCH(val, wordsB)), 1, 0))),
    maxWords, MAX(COUNTA(wordsA), COUNTA(wordsB)),
    commonWords / maxWords
)

How this formula works:

• LET: Assigns local variables to keep the formula readable and performant.
• TEXTSPLIT: Splits the text values into arrays of words based on space characters (" "). We wrap them in LOWER to ignore case sensitivity.
• REDUCE and LAMBDA: Iterates through each word in the first string (wordsA) and counts how many times those words match anywhere in the second string (wordsB).
• maxWords: Finds the maximum total word count between the two fields to serve as our denominator.
• The final line divides the matching word count by the maximum word count, generating a similarity ratio.

You can wrap this logic in an IF statement to evaluate your threshold. For example, if your threshold is 0.70, you can output "Match" or "Check Manually":

=IF([Similarity Formula] >= 0.70, "Valid Match", "No Match")

Which Method Should You Choose?

Your choice depends on the structure of your workbook and how comfortable you are with developer tools:

Conclusion

Fuzzy matching with a similarity threshold transforms how we handle dirty and unstructured data in Excel. For regular database cleaning pipelines, Power Query's Fuzzy Merge is unmatched in convenience and optimization. For automated, on-the-sheet lookups, using a VBA Levenshtein Distance function or a modern 365 token-split formula provides the flexibility needed to handle complex datasets on the fly.