Fuzzy Match

Prepare data
Match messy customer or vendor records against a master reference list with text similarity scoring, review bands, and threshold controls in Excel.

An analyst or operations coordinator has two lists that should describe the same entities but rarely agree exactly: incoming vendor invoices against an approved vendor master, or customer prospect lists against the CRM. Because standard formulas like XLOOKUP and VLOOKUP require character-exact matches, variations such as abbreviations (“St” vs “Street”), missing suffixes (“Inc”, “Ltd”), swapped word order, punctuation, or typos result in #N/A errors and hours of manual checking. This template performs automated fuzzy matching between two lists, assigns a similarity score to every record, and automatically categorizes matches as accepted, borderline (flagged for review with top runner-up candidates), or unmatched.

Result preview

When the workbook opens, every row in the Source table appears alongside its best match from the Reference list, the confidence score, and a clear status. A summary table reports the breakdown: 40 confident matches, 10 borderline records requiring human judgment, and 10 records with no counterpart in the master catalog. The Review sheet and the notebook task pane present the borderline records with their top three candidate options and individual similarity scores, allowing reviewers to verify near-matches before acceptance.

What this template does

  • Matches customer, vendor, or product lists pasted into the Source table against a master Reference list, accommodating up to 500 rows in each list.
  • Automatically handles common real-world naming discrepancies including street and company abbreviations (“St” vs “Street”, “Ave” vs “Avenue”), omitted corporate suffixes (“LLC”, “Inc”, “Corp”), swapped word orders, and spelling typos.
  • Calculates a multi-column weighted similarity score across Name, Address, and City fields.
  • Categorizes each source record into one of three statuses based on user-configurable confidence thresholds: Matched (score >= 85%), Needs review (65% to 85%), or No match (score < 65%).
  • Compiles a dedicated Review queue showing borderline items with up to three reference candidates and their respective similarity scores.
  • Allows users to toggle which columns are included in comparison directly from the Settings sheet.

Why Python

Fuzzy text matching requires string normalization, tokenization, abbreviation expansion, and sequence similarity calculations that spreadsheet formulas cannot handle without hundreds of helper columns or fragile approximations. Standard spreadsheet lookups fail when a name differs by a single letter or word ordering. In Python, standard library difflib and token sorting provide deterministic, robust similarity metrics across multiple attributes in a single reactive calculation.

Paste your lists into the Source and Reference tables, or adjust confidence thresholds on the Settings sheet. The matched records, summary KPIs, and review queue update automatically.

Operating workflow

Author: A data analyst or analytics engineer configures default confidence thresholds, customizes column weights, and adjusts normalization rules in the notebook.

Workbook user: An operations manager or accountant pastes incoming lists into Source and Reference, adjusts threshold switches on Settings, and reviews flagged borderline items on the Review sheet.

The workbook can also be viewed in App mode to provide a streamlined, card-like presentation for operational users. App mode is a presentation setting rather than permissions, source access, or workbook trust.

Download the Excel template

Inputs and assumptions

Input Location Description
Source list Source!A4:C504 List of entities to match, accommodating up to 500 rows with Name, Address, and City
Reference master Reference!A4:D504 Master reference list with unique ID, Name, Address, and City
Settings & thresholds Settings!A5:B10 Acceptance threshold (85%), review threshold (65%), and column comparison toggles

The input ranges accommodate up to 500 rows each, allowing users to paste their own datasets directly over the sample tables. Trailing blank rows beyond active data are automatically trimmed before matching.

Notebook implementation

The Python for Excel notebook binds the Source data, Reference master, and Settings:

inputs = bf.inputs(
    source=bf.ref("Source!A4:C504"),
    reference=bf.ref("Reference!A4:D504"),
    settings=bf.ref("Settings!A5:B10", headers=True),
)

The matching pipeline expands common abbreviations, strips company suffixes, computes token-sorted similarity scores across active columns, and publishes outputs back to Excel:

publication = bf.publish(
    outputs={
        "summary": summary_out,
        "matches": matches_pad,
        "review": review_pad,
    }
)

How the calculation/model works

  1. Table Ingestion & Cleaning: Reads the Source and Reference ranges, extracting header names dynamically and dropping trailing all-blank rows.
  2. Text Normalization: Applies NORMALIZATION_MAP from the editable rules section to expand street abbreviations (“St” -> “street”, “Ave” -> “avenue”, “Pkwy” -> “parkway”) and strip corporate designators (“Inc”, “LLC”, “Corp”, “Ltd”).
  3. Similarity Scoring: Evaluates candidate pairs across all active columns using character sequence matching and token-sorted matching to handle word transpositions and typos. Weighted column scores combine into an overall confidence metric between 0.0 and 1.0.
  4. Status Assignment: Compares the top candidate score against the configured thresholds:
    • Matched: Score >= Accept threshold (default 85%).
    • Needs review: Review threshold <= Score < Accept threshold (default 65%–85%).
    • No match: Score < Review threshold.
  5. Output Publication: Generates the complete matched table on Fuzzy Match, summary metrics, and a 10-column candidate breakdown for borderline records on the Review sheet.

Validation / expected results

Metric / Check Expected baseline
Matched records 40
Needs review records 10
No match records 10
Total source records 60

Raising the accept threshold to 90% reclassifies 11 near-matches into the Needs review band, raising the review count to 21. Adding a missing company counterpart to the Reference table immediately converts an unmatched record to Matched. Excluding the Address column from comparison changes the winning candidate for ambiguous records where name and address point to different entities.

Limitations

  • Difflib sequence matching is optimized for lists up to thousands of rows; massive datasets with millions of pairs should be pre-filtered or short-listed using blocking techniques.
  • Matching relies on textual similarity; phonetic algorithms (Soundex/Metaphone) and geographic distance validation are non-goals for this template.
  • Accepted matches are published to dedicated output ranges and do not overwrite source tables in place.

When to use this approach

Use this template when reconciling vendor invoices against accounts payable masters, deduplicating customer lead lists against CRM databases, or integrating third-party datasets where naming conventions differ slightly.

Changelog

  • 2026-10-17 (Issue #19): Initial release of Fuzzy Match template with multi-attribute text similarity scoring, review queue, and configurable confidence thresholds.