How to Compare Large Excel Lists with Fuzzy Matching
Question details
The user needs to compare large data lists to find approximate matches where exact names differ due to spelling errors or missing words.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Comparing extensive databases or lists (e.g., thousands of rows) to identify similar organization names, customer records, or text strings that do not match exactly.
- Observed behavior
- Standard lookup functions fail to return matches for items with slight spelling discrepancies, requiring a fuzzy matching approach to bridge the gap and calculate similarity scores.
Ensure your data lists are properly formatted as Excel Tables (Ctrl + T) and that you have a basic idea of what similarity threshold (e.g., 80% match) is acceptable for your analysis.
Use Power Query Fuzzy Merge
Leverage Power Query's built-in Fuzzy Merge feature to combine and compare tables based on approximate matches natively within Excel.
Power Query offers a native 'Fuzzy Matching' option when merging queries. This allows you to set a similarity threshold between 0.00 and 1.00 and ignore case or text parts, making it ideal for cleaning dirty data.
Select your first table, go to the Data tab, and click 'From Table/Range'. Once the Power Query Editor opens, choose 'Close & Load To...' and select 'Only Create Connection'. Repeat this for your second table.
In Excel, go to Data > Get Data > Combine Queries > Merge. In the Merge dialog box, select your primary table from the first drop-down and your secondary table from the second drop-down.
Click on the column headers you want to compare in both table previews. Check the box that says 'Use fuzzy matching to perform the merge'.
Expand the 'Fuzzy matching options' to adjust the similarity threshold (default is 0.80). A lower number yields more matches but higher false positives. Click OK to proceed.
In the Power Query Editor, click the expand icon on the newly merged column header to select the specific data you want to display, then click 'Close & Load' to return the cleaned list to Excel.
Use the Microsoft Fuzzy Lookup Add-In
Install the dedicated Fuzzy Lookup Add-In for Excel to calculate exact similarity scores and map mismatched strings directly in a worksheet pane.
Manually Validate Low-Confidence Matches
Use conditional formatting and manual review to ensure data integrity for matches that fall below the acceptable similarity threshold.
Try WPS Office for Your Data Processing Needs
While advanced features like Power Query's Fuzzy Join are specific to Microsoft Excel, WPS Spreadsheet provides a powerful, fast, and free alternative for standard data cleaning, lookup functions, and everyday spreadsheet management.
- 1. Download and Install: Get WPS Office from the official website and install the lightweight suite on your device.
- 2. Open Your Excel File: Launch WPS Spreadsheet and open your existing .xlsx file without worrying about formatting loss.
- 3. Process Your Data: Utilize built-in data handling tools, filters, and standard lookup formulas to clean and organize your lists effectively.
Frequently Asked Questions
What is the similarity threshold in Power Query Fuzzy Matching?
The similarity threshold is a value between 0.00 and 1.00 that determines how closely text strings must match. A threshold of 1.00 requires an exact match, while lower values (like 0.70) allow for more spelling differences and missing words, though this may increase false positives.
Does fuzzy matching guarantee 100% accuracy?
No. Fuzzy matching relies on algorithms to guess similarities based on character and word overlap. It is highly recommended to manually validate the results, especially for data points with lower similarity scores.
Can I use wildcard characters instead of Fuzzy Merge?
Yes, for simple approximate matches, you can use wildcard characters (such as '*' or '?') combined with functions like VLOOKUP or XLOOKUP. However, wildcards are best for strings that contain a known pattern, whereas fuzzy matching is better for random typos or inverted word orders.
Why didn't Power Query find a match even with a low threshold?
If the textual differences are too severe (for example, comparing 'IBM' to 'International Business Machines'), the algorithm won't connect them regardless of the threshold. You will need to use a Transformation Table to define these custom mappings before merging.
