Power Query Problems

Find Partial or Approximate Text Matches Between Excel Columns

John WilsonJohn Wilson Nov 18, 2026 869 views

Question details

Find corresponding items in two Excel columns when their descriptions are not identical.

Find Partial or Approximate Text Matches Between Excel Columns
Product
Microsoft Excel Power Query
Device & OS
not provided
Scenario
Compare lists containing differences in wording, abbreviations, and number formats.
Observed behavior
Exact-match formulas can miss related descriptions when their text differs.
Before you start

Check that each list has a header and that the descriptions are stored as text. Where practical, remove extra spaces and standardize abbreviations and number formats before comparing the lists.

Solution 1Recommended

Use Power Query Fuzzy Merge for Approximate Matches

Use fuzzy matching to compare large lists when descriptions differ slightly rather than appearing as exact substrings.

1
Load both lists

Select each list in Excel and choose Data > From Table/Range. Confirm that the table has headers, then load each list into Power Query.

2
Start a merge

In Power Query Editor, select the first query and choose Home > Merge Queries. Select the second query and click the description column in each table.

3
Enable fuzzy matching

Check Use fuzzy matching to perform the merge. Open Fuzzy matching options and set a similarity threshold; a higher threshold requires closer matches.

4
Review the results

Select OK, then use the expand button on the merged column to display matching descriptions. Review the suggested matches and adjust the threshold if needed.

5
Return results to Excel

Choose Home > Close & Load to place the results in the workbook.

Use Power Query Fuzzy Merge for Approximate Matches
Check ambiguous matches: Fuzzy matching suggests similar text but does not guarantee that two descriptions refer to the same item. Standardize values such as “two” and “2” beforehand and verify uncertain results.
Free Microsoft Office alternative

Compare Spreadsheet Text with WPS Office

WPS Office is a free, lightweight Office alternative with a familiar spreadsheet interface. Open compatible Excel files and use text-search formulas while moving between Office suites. Microsoft Excel is still needed for the Power Query fuzzy merge described above.

Use SEARCH and ISNUMBER formulas to check for text within descriptionsCompatible with Microsoft Excel XLS and XLSX formatsFamiliar spreadsheet interface for an easier transitionFree, lightweight office suite
microsoft office alternative - wps office

Frequently Asked Questions

How do I control how close Power Query fuzzy matches must be?

In the Merge Queries dialog, enable fuzzy matching and open Fuzzy matching options. Increase the similarity threshold for stricter matches or lower it to include less similar text, then review the results.

Why does the SEARCH formula return no match for similar descriptions?

SEARCH looks for the exact search text within another text string, ignoring case. It will not necessarily match abbreviations, reordered words, or “two” against “2”; standardize the text or use Power Query fuzzy merge instead.

Can the FILTER formula return more than one match?

Yes. FILTER returns every cell in C2:C3500 that contains the text in A2, spilling the results into cells below the formula. Make sure those cells are empty.

How can I clean descriptions before matching them?

Use Excel formulas such as TRIM and CLEAN to remove extra spaces and nonprinting characters, and LOWER to standardize case. Replace inconsistent abbreviations or number words with a consistent form before merging.