Fuzzy Match for Excel
Overview
Fuzzy matching versions of XMATCH and XLOOKUP, with unlimited free use. FUZZY.LOOKUP and FUZZY.MATCH are Excel functions similar to the native XLOOKUP and XMATCH, but with approximate text matching (fuzzy matching) capabilities. The algorithms run locally in Excel, so your data never leaves your spreadsheet.
Features
| Feature | Description |
|---|---|
| 🎯 Familiar Syntax | Similar to XLOOKUP and XMATCH |
| 🆓 Free | Unlimited free use |
| 🔒 Private | No data is shared outside your spreadsheet |
| 🚀 Fast | Optimized algorithm handles large datasets |
| ✅ Transparent | Outputs lookup, match, and similarity for quick comparison |
Functions
FUZZY.MATCH (like XMATCH, with fuzzy matching)
| Aspect | Description |
|---|---|
| Inputs | lookup_value, lookup_array, [threshold] |
| Outputs | Lookup, Match, Similarity, and Row as a custom data type |
| Behavior | Searches for the lookup_value in the lookup_array using fuzzy text matching |
| Threshold | Adjusts how close the match must be |
| Result | Match row in lookup_array, use with INDEX(), etc. |
FUZZY.LOOKUP (like XLOOKUP, with fuzzy matching)
| Aspect | Description |
|---|---|
| Inputs | lookup_value, lookup_array, return_array, [threshold] |
| Outputs | Lookup, Match, Similarity, and values from matching row in return_array |
| Behavior | Searches for the lookup_value in the lookup_array using fuzzy text matching |
| Threshold | Adjusts how close the match must be |
| Result | Retrieves selected columns from the matching row |
Applications
Find and remove duplicates: Use a formula like
=BOARDFLARE.FUZZY.MATCH([@Name],FILTER([Name],ROW([Name]) <> ROW()),0.7)to find similar matches in a Name column, excluding the current row. TheFILTER([Name],ROW([Name]) <> ROW())creates an array of all rows except the current one.Match leads with your CRM: Match marketing leads with existing CRM companies using FUZZY.LOOKUP. Set
lookup_valueto the lead company name andlookup_arrayto CRM company names. Setreturn_arrayto the CustomerID column for easy CRM import. Use the same approach to match lead names with CRM contacts.Find applicants in HR system: Check if job applicants exist in your HR system. Set
lookup_valueto the applicant name andlookup_arrayto previous applicants from the HR system. Setreturn_arrayto additional HR columns you want returned (e.g., date of last application, position applied for).Master Data Management (MDM): Use fuzzy matching to identify and link related records across different systems or datasets. This helps with deduplicating, entity resolution, linking, cleansing, and standardizing data.
Limitations
The algorithm works better when searching for substrings in lookup_array that match the lookup_value, rather than the reverse:
| Scenario | lookup_value | lookup_array | Similarity |
|---|---|---|---|
| ✅ Better | “Microsoft” | “Microsoft Corporation” | 0.99 |
| ⚠️ Lower | “Microsoft Corporation” | “Microsoft” | 0.43 |
Tip: Where possible, use the shorter string as the lookup_value. Use Excel functions to remove unnecessary words (e.g., Corporation, Inc.) from the lookup_value.
Also remember that fuzzy matching accuracy varies by use case—it may work excellently in one situation and poorly in another.
Installation
The Fuzzy Match add-in is available for Excel on Windows, Mac, and the web. Install from the Microsoft AppSource store or directly from Excel.
Algorithm
The add-in uses the Bitap algorithm, which calculates Levenshtein distance. See Fuzzy Matching for details on other algorithms under consideration.