Find Partial or Approximate Text Matches Between Excel Columns
Question details
Find corresponding items in two Excel columns when their descriptions are not identical.

- 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.
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.
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.
Select each list in Excel and choose Data > From Table/Range. Confirm that the table has headers, then load each list into Power Query.
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.
Check Use fuzzy matching to perform the merge. Open Fuzzy matching options and set a similarity threshold; a higher threshold requires closer matches.
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.
Choose Home > Close & Load to place the results in the workbook.

Use SEARCH and FILTER for Substring Matches
Use a formula when the text in one column appears within the description in the other column.
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.

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.




