Matching Company Names in Excel

Company-name matching usually fails at the edges: punctuation, legal suffixes, parenthetical locations, an ampersand in one system and “and” in another. Excel can reduce that noise. It cannot prove that two similar names identify the same legal entity.

The practical goal is to create a candidate key that makes likely matches easy to review, then preserve the original names and an identifier so a person can decide.

Start with the match contract

Before writing a formula, decide what counts as a match for this workbook:

• Is this a candidate list for human review or an automatic merge?
• Which stable identifier, address, tax ID, domain, or account number can break a tie?
• Which legal suffixes or location notes may be removed safely in this data set?
• What should happen when two different source names collapse to the same key?

For high-consequence uses—payments, contracts, compliance, or customer records—name normalization should only create candidates. It should not be the sole identity check.

Version boundary

The LET function is available in Microsoft 365, Excel 2024, and Excel 2021. REGEXREPLACE is currently documented for Excel for Microsoft 365 and Microsoft 365 for Mac. If a workbook must run in older or mixed Excel installations, use simpler formulas, Power Query, or a governed data-cleaning process instead of assuming regex will work everywhere. (LET, REGEXREPLACE)

Build a readable candidate key

Assume the raw company name is in A2. This Microsoft 365 formula removes a trailing parenthetical, normalizes &, removes a small reviewed suffix list, strips punctuation, and converts the result to uppercase:

=LET(
  raw, A2,
  noParen, REGEXREPLACE(raw, "\s*\([^)]*\)\s*$", ""),
  ampersandFixed, SUBSTITUTE(noParen, "&", "AND"),
  noSuffix, REGEXREPLACE(ampersandFixed, "\b(inc|incorporated|llc|ltd|limited|corp|corporation)\.?\b", "", 0, 1),
  UPPER(REGEXREPLACE(noSuffix, "[^A-Za-z0-9]", ""))
)

For example, the formula turns Acme & Sons LLC (Chicago) into ACMEANDSONS.

The suffix list is intentionally small. Add terms only after reviewing how they behave in your data. “Company,” “group,” or geographic words can be meaningful parts of a name, not disposable noise.

Find candidates; do not hide collisions

Put the normalized key beside each source name. Then use XLOOKUP or a pivoted review table to find candidates in the other source. XLOOKUP can return a value from the corresponding row, but a single lookup result does not tell you whether the key was unique. (XLOOKUP)

Add a collision count before you accept a match:

=COUNTIF(OtherSystem[Candidate_Key], [@[Candidate_Key]])

Use the result this way:

Count	Meaning	Next step
0	No normalized candidate	Review for a new entity, spelling issue, or a missing transformation.
1	One candidate	Compare the original names and a stable second identifier.
2 or more	Collision	Do not auto-match; investigate the records.

Test the transformation before using it

Keep a small test sheet with examples that should match and examples that must not. Include punctuation changes, a parenthetical location, a real suffix, a name where “and” matters, and two distinct entities that would collide after cleanup.

If a formula changes, rerun the test sheet and review the collision list. That turns a clever formula into a maintainable data-quality control.

The most useful Excel matching workflow is not the longest formula. It is the one that makes its assumptions visible and leaves ambiguous records for a person to resolve.