How-to

Advanced Data Transformation And CleaningDeep dive

Fuzzy Match Names Between Two Excel Files with Python

Join two spreadsheets when the names do not quite match — Acme Ltd vs ACME Limited — with rapidfuzz: normalise names, choose a scorer, set a cut-off, score every pair at once, and send doubtful matches for review.

Joins, lookups & comparisons Data cleaning pandas

The sales workbook says Acme Ltd, the finance workbook says ACME Limited, and the CRM export says Acme Ltd. with a trailing full stop. A merge on the name column matches none of them, and VLOOKUP in Excel does no better. When there is no shared ID, the only way to join the files is to decide which names are close enough to be the same organisation — and to be honest about the ones that are not certain. This guide, part of Merging and Joining Excel DataFrames, builds a fuzzy join with the rapidfuzz library that is fast, tunable and reviewable.

Prerequisites

Bash

pip install pandas openpyxl rapidfuzz

rapidfuzz is a fast, MIT-licensed string matching library with the same scorers people know from fuzzywuzzy. The two example files:

Python

import pandas as pd

pd.DataFrame({
    "Customer": ["Acme Ltd", "Globex Corporation", "Initech", "Umbrella Holdings plc",
                 "Stark Industries", "Wayne Enterprises Inc."],
    "Revenue": [120500, 98200, 43100, 87150, 150300, 64000],
}).to_excel("sales.xlsx", index=False)

pd.DataFrame({
    "Account name": ["ACME Limited", "Globex Corp.", "Initek Inc", "Umbrela Holdings",
                     "Wayne Enterprise", "Soylent Green Co"],
    "Credit limit": [50000, 75000, 20000, 40000, 60000, 15000],
}).to_excel("finance.xlsx", index=False)

sales = pd.read_excel("sales.xlsx")
finance = pd.read_excel("finance.xlsx")

Step 1 — Normalise before you compare

Most of the difference between two spellings of a company name is noise: capitalisation, punctuation and legal suffixes. Removing it first makes exact matches out of many pairs and leaves the fuzzy scorer to handle only genuine variation. Keep the original column and add a normalised key beside it.

Python

import re

SUFFIXES = r"\b(ltd|limited|inc|incorporated|corp|corporation|co|company|plc|llc|gmbh)\b"

def normalise(name: str) -> str:
    text = str(name).lower()
    text = re.sub(r"[^\w\s]", " ", text)          # punctuation to spaces
    text = re.sub(SUFFIXES, " ", text)            # legal forms add nothing
    return re.sub(r"\s+", " ", text).strip()

sales["key"] = sales["Customer"].map(normalise)
finance["key"] = finance["Account name"].map(normalise)
print(sales[["Customer", "key"]])

exact = sales.merge(finance, on="key", how="inner")
print(f"{len(exact)} of {len(sales)} match exactly after normalising")

On this data normalisation alone joins two of the six customers — Acme Ltd with ACME Limited and Globex Corporation with Globex Corp. — leaving the typos (Initek, Umbrela, Wayne Enterprise) and the genuinely absent Stark Industries for the fuzzy step. Tailor the suffix list to your data — adding words such as group or holdings helps some files and hurts others where those words distinguish two real companies.

Step 2 — Choose a scorer

rapidfuzz offers several ways to score similarity from 0 to 100, and they disagree in instructive ways. ratio compares whole strings character by character; token_sort_ratio ignores word order; token_set_ratio ignores extra words; WRatio combines several and is a sensible default for names.

Python

from rapidfuzz import fuzz

pairs = [("acme", "acme"), ("globex", "globex"), ("umbrella holdings", "umbrella"),
         ("stark industries", "industries stark"), ("wayne enterprises", "wayne enterprise")]
for left, right in pairs:
    print(f"{left!r:22} {right!r:22}",
          round(fuzz.ratio(left, right)), round(fuzz.token_sort_ratio(left, right)),
          round(fuzz.token_set_ratio(left, right)), round(fuzz.WRatio(left, right)))

token_set_ratio scores umbrella holdings against umbrella as a perfect match, because one set of words contains the other — useful for names with extra words, and dangerous for short names that are contained in many others. Try the scorers on a sample of known pairs from your own data before choosing one.

Step 3 — Find the best match for each name

process.extractOne scores one query against a list of choices and returns the best choice, its score and its position. A score_cutoff returns None when nothing is good enough, which is cleaner than filtering afterwards.

Python

from rapidfuzz import process

choices = finance["key"].tolist()

def best_match(key: str, cutoff: float = 75):
    found = process.extractOne(key, choices, scorer=fuzz.WRatio, score_cutoff=cutoff)
    if found is None:
        return pd.Series({"match_key": None, "score": 0.0, "finance_row": None})
    choice, score, index = found
    return pd.Series({"match_key": choice, "score": round(score, 1), "finance_row": index})

matches = sales.join(sales["key"].apply(best_match))
print(matches[["Customer", "match_key", "score"]])

Umbrella Holdings plc and Wayne Enterprises Inc. find their misspelt counterparts with scores of 97, Initech reaches Initek at about 77, and Stark Industries has no counterpart in the finance file — with a cut-off of 75 it correctly stays unmatched instead of being paired with the least-bad candidate. Without a cut-off, every name gets some match — the single most common way fuzzy joins produce confident nonsense.

Step 4 — Accept, review or reject by score band

One threshold forces a choice between missing real matches and accepting false ones. Two thresholds do better: accept high scores automatically, reject low scores, and send the band in between to a person. The bands below suit company names; calibrate them on your own data.

Python

ACCEPT, REVIEW = 92, 75

def decision(score: float) -> str:
    if score >= ACCEPT:
        return "accepted"
    if score >= REVIEW:
        return "review"
    return "no match"

matches["decision"] = matches["score"].map(decision)
joined = matches.merge(
    finance.reset_index().rename(columns={"index": "finance_row"}),
    on="finance_row", how="left", suffixes=("", "_finance"),
)
print(joined[["Customer", "Account name", "score", "decision", "Credit limit"]])
print(joined["decision"].value_counts())

Here Initech lands in the review band: Initek Inc is probably the same company with a typo, but a score in the seventies is also what two different short names can produce, so a person should confirm it. The other four matches are accepted and Stark Industries is reported as unmatched.

Keeping the finance row position from extractOne and joining on it — rather than joining on the matched text — avoids a subtle bug when two finance rows normalise to the same key.

Step 5 — Score everything at once for large files

extractOne in a loop is fine for a few thousand names. For tens of thousands on each side, process.cdist computes the whole score matrix in compiled code, optionally across all CPU cores, and NumPy then picks the best column per row.

Python

import numpy as np

scores = process.cdist(sales["key"], finance["key"], scorer=fuzz.WRatio, workers=-1)
best = scores.argmax(axis=1)
sales["cdist_match"] = finance["Account name"].to_numpy()[best]
sales["cdist_score"] = scores[np.arange(len(sales)), best].astype(float).round(1)
print(sales[["Customer", "cdist_match", "cdist_score"]])

The matrix holds one score per pair, so memory grows with the product of the two lengths — 50,000 × 50,000 names is 2.5 billion scores. Beyond a few tens of thousands per side, split both files into blocks that can only match within themselves — same country, same postcode area, same first letter — and run cdist per block. Blocking also improves accuracy, because it removes impossible candidates before they can score well by coincidence.

Step 6 — Export a review sheet people can work through

The review band needs a human decision, and Excel is where that person works. Write the candidates side by side with the score and an empty column for the verdict, and read the verdicts back on the next run.

Python

review = joined.loc[joined["decision"] == "review",
                    ["Customer", "Revenue", "Account name", "Credit limit", "score"]].copy()
review["Same company? (Y/N)"] = ""

with pd.ExcelWriter("match_review.xlsx", engine="openpyxl") as writer:
    joined.loc[joined["decision"] == "accepted"].to_excel(writer, sheet_name="Accepted", index=False)
    review.to_excel(writer, sheet_name="Review", index=False)
    joined.loc[joined["decision"] == "no match", ["Customer", "Revenue"]].to_excel(
        writer, sheet_name="Unmatched", index=False)
    writer.sheets["Review"].freeze_panes = "A2"

print(pd.ExcelFile("match_review.xlsx").sheet_names)

Store confirmed pairs in a small mapping table and apply it before fuzzy matching on later runs, so each pair is only ever reviewed once. Over time the mapping becomes the shared ID the two systems never had. Formatting the review sheet for easier reading is covered in Write a Formatted Excel Report with xlsxwriter.

Common pitfalls

Symptom	Cause	Fix
Every row gets a match, many wrong	No score cut-off	Use `score_cutoff` and a no-match band
Short names match unrelated long ones	`token_set_ratio` treats subsets as perfect	Use `WRatio` or `token_sort_ratio`; require minimum length
Obvious matches score low	Suffixes and punctuation not removed	Normalise before scoring
Two companies merged into one	Normalisation removed a distinguishing word	Shorten the suffix list; review merged keys
Memory error on large files	`cdist` matrix too big	Block by country, postcode or first letter
Results change between runs	Ties between equally scored candidates	Break ties explicitly, e.g. by a second field

Performance and scale notes

rapidfuzz is implemented in C++ and is dramatically faster than pure-Python matching; extractOne handles thousands of queries per second against lists of thousands of choices. cdist with workers=-1 is the fastest option for all-pairs scoring but is bounded by memory, which blocking solves. Normalising once into a key column, rather than inside the scorer through a processor, avoids repeating the same regex work for every pair.

Conclusion

Normalise names first — case, punctuation and legal suffixes — because it turns many fuzzy problems into exact ones. Pick a scorer by testing known pairs, use extractOne with a cut-off so unmatched names stay unmatched, and split decisions into accepted, review and no-match bands. Scale with cdist and blocking, write the review band to a sheet people can work through, and keep confirmed pairs as a mapping so each decision is made only once.

Frequently asked questions

Is rapidfuzz the same as fuzzywuzzy? It provides the same scorers with a compatible API, but it is implemented in C++, much faster, and MIT-licensed. For new code rapidfuzz is the better choice.

What score threshold should I use? There is no universal number. Score a sample of pairs you already know are matches and non-matches, and choose thresholds that separate them; for company names, accepting from the low nineties and reviewing from the mid seventies is a reasonable starting point.

Can I fuzzy match on more than one column? Yes. Score names and addresses separately and combine the scores, or block on an exact field such as postcode and fuzzy match names only within each block.

Can Excel do fuzzy matching itself? Power Query has a fuzzy merge option with a similarity threshold. Python gives you more control over normalisation, scorers and review workflows, and runs unattended.

Up one level: Merging and Joining Excel DataFrames — every way to combine spreadsheet data.
Merge Two Excel Files on a Common Column in Python — the exact-match join this page extends.
Find Rows in One Excel File Missing from Another — reporting the unmatched rows.
Strip Whitespace and Normalise Text Columns with pandas — deeper text normalisation.
Find Duplicate Rows in Excel with Python — near-duplicates within a single file.

More on joins, lookups & comparisons

More on joins, lookups & comparisons →