Power Query Problems

Fuzzy Match Comma-Separated Company Names in Excel Power Query

Ayan MasoodAyan Masood Nov 18, 2026 870 views

Question details

Replace partial, comma-separated company names with official names and codes from a lookup table using fuzzy matching.

Fuzzy Match Comma-Separated Company Names in Excel Power Query
Product
Microsoft Excel Power Query
Device & OS
not provided
Scenario
Each user record contains one or more company names in a single column, while a separate lookup table contains official company names and codes.
Observed behavior
Some company names are incomplete, so they need to be matched approximately before the results are combined back into user records.
Before you start

Load the user data and the company lookup table into Power Query, and check that the company-name columns use the Text data type. Confirm that the lookup table has separate official name and code columns.

Solution 1Recommended

Split and fuzzy merge company names in Power Query

Use a fuzzy merge to find the closest lookup entry for each company, then regroup the results.

1
Identify the original records

Open the user-data query in Power Query Editor. If usernames can appear in multiple records, select Add Column > Index Column to give each original row a unique identifier.

2
Split company names into rows

Select the company column, then choose Split Column > By Delimiter. Select Comma, open Advanced options, and choose to split into rows. Select the resulting column and choose Transform > Format > Trim to remove surrounding spaces.

3
Merge with the lookup table

Choose Home > Merge Queries. Select the company column in the user query and the official CompanyName column in the lookup query, use a Left Outer join, and enable fuzzy matching.

4
Set fuzzy matching options

Expand Fuzzy matching options, enable Ignore case and Match by combining text parts to ignore spaces, and set the maximum number of matches to 1. Try a similarity threshold of 0.5 as a starting point.

5
Expand and review matches

Use the expand button on the merged column to add CompanyName and CompanyCode. Review the matches, especially those produced at a low threshold; adjust the threshold or correct lookup data if needed.

6
Keep unmatched names

Choose Add Column > Custom Column and create a final-name column with a formula such as if [CompanyName] = null then [company] else [CompanyName]. Leave the code blank when no lookup match exists.

7
Regroup and load

Choose Home > Group By and group by the original row index and username, or by username alone if each user has one source record. Use the All Rows operation, then add custom columns with Text.Combine to join the final names and codes. Convert null codes to empty text before combining, remove helper columns, and select Home > Close & Load.

Split and fuzzy merge company names in Power Query
Check approximate matches: A threshold of 0.5 can produce incorrect matches. Review the results and raise the threshold when company names are similar but refer to different organizations.
Free Microsoft Office alternative

Work with company lookup tables in WPS Spreadsheet

WPS Office is a free, lightweight Office alternative with a familiar spreadsheet interface. Open compatible Excel workbooks and continue working with company lists; Excel Power Query fuzzy matching remains a Microsoft Office workflow.

Review and edit company lists in a familiar spreadsheet interfaceCompatible with Microsoft Excel XLS and XLSX formatsMove existing workbooks into a lightweight, easy-to-use office suiteUse WPS Office for free
microsoft office alternative - wps office

Frequently Asked Questions

Why should I split company names into rows before merging?

Fuzzy Merge compares column values, so splitting by comma into rows lets Power Query match each company separately. Use Split Column > By Delimiter and select the rows option.

What happens when a company name has no match?

A Left Outer merge keeps the original row and returns null for the lookup fields. Use Add Column > Custom Column to keep the original company text when CompanyName is null.

How can I reduce incorrect fuzzy matches?

Open the merge's Fuzzy matching options and increase the similarity threshold. Limit the number of matches to 1, then review the expanded official names against the original company values.

How do I combine the matched names back into one cell?

Use Home > Group By with All Rows, then add a custom column that applies Text.Combine to the final-name list with a comma and space as the separator.

Should I group by username or by an index column?

Group by username alone when each user has one source record. If a username can appear in multiple records, add an index before splitting and group by both the index and username to keep those records separate.