Use Excel Fuzzy Lookup to Match Similar Text Data
Question details
The user needs to match text values that are similar but not identical, such as inconsistent customer names or product descriptions.
- Product
- Microsoft Excel Power Query and Fuzzy Lookup
- Device & OS
- not provided
- Scenario
- The user wants to combine or compare two tables when an exact lookup cannot match variations in text.
- Observed behavior
- Exact matching does not connect records with spelling variations, abbreviations, extra spaces, or slightly different descriptions.
Format both source ranges as Excel tables and make sure the columns to be compared contain text data. Clean obvious issues such as leading or trailing spaces before setting a fuzzy similarity threshold.
Merge tables with fuzzy matching in Excel Power Query
Use this method when your Excel version includes Power Query and you need to match two tables with inconsistent text values.
Power Query can merge two queries using approximate text matching. Lower similarity thresholds return more possible matches, while higher thresholds reduce weak matches and false positives.
Select a cell in the first Excel table, then choose Data > From Table/Range. In the Power Query Editor, select Home > Close & Load To > Only Create Connection.
Select a cell in the second table and choose Data > From Table/Range. Choose Home > Close & Load To > Only Create Connection after confirming the text column is available.
In Excel, select Data > Get Data > Combine Queries > Merge, choose the first and second table queries, and select the text column in each table that should be matched.
Select the Use fuzzy matching to perform the merge check box, then open Fuzzy matching options. Set a similarity threshold, such as 0.80, and adjust options such as ignoring case or matching by combining text parts when available.
Select OK, use the expand button on the merged column to return needed fields, then choose Home > Close & Load. Review the matched rows and correct or filter any incorrect approximate matches.
Use the legacy Excel Fuzzy Lookup add-in
Use this option only if your organization already has the Microsoft Fuzzy Lookup add-in installed and Power Query fuzzy merge is unavailable.
Prepare and Compare Text Data in WPS Spreadsheet
WPS Office is a free, lightweight Office alternative with a familiar spreadsheet interface for organizing, cleaning, and comparing text data before moving files between compatible Office environments. Open existing Excel workbooks seamlessly and use text formulas, sorting, filters, and duplicate checks to standardize values for more reliable matching.
Frequently Asked Questions
What similarity threshold should I use for an Excel fuzzy match?
Start around 0.80 in the Power Query Merge dialog under Use fuzzy matching to perform the merge. Raise the threshold when you get incorrect matches, or lower it gradually when legitimate variations are being missed.
Where is the fuzzy matching option in Power Query?
Load both tables as queries, then choose Data > Get Data > Combine Queries > Merge. After selecting the matching columns, select the Use fuzzy matching to perform the merge check box.
Why does Power Query fuzzy matching return incorrect records?
Fuzzy matching compares text similarity, so unrelated values can sometimes appear close enough to match. Increase the similarity threshold, clean spaces and abbreviations, and manually review merged results.
Can I use fuzzy lookup for names with different spellings?
Yes. Fuzzy lookup is useful for variants such as abbreviations, minor misspellings, different word order, or inconsistent punctuation. Results should still be validated because similar names may belong to different people or companies.
