When your accounts payable list and your master vendor table disagree on how a supplier is spelled, a simple lookup misses the match. This guide shows a four-step vendor reconciliation workflow in Google Sheetsand how the AP Vendor Reconciliation template automates it in one click.
Why Vendor Reconciliation Is Hard
Vendor records are entered by peopleand people abbreviate. One list contains
Acme Corp
, another contains
Acme Corporation
. One has
IBM
, the other has
International Business Machines
. Punctuation, legal suffixes and typos pile up on top of that.
Reconciling vendors matters because every downstream step inherits the result. Duplicate vendor records mean duplicate payments, misallocated spend and messy 1099 reporting. A vendor that appears under three names in your AP list is really one supplier, but a naive reconciliation treats it as three.
The Reconciliation Workflow
Reliable vendor reconciliation needs four stages:
- Standardise every vendor name so both lists use the same representation.
- Remove duplicates inside the AP list so the same supplier is not matched twice.
- Fuzzy-match the AP list against the master vendor table with a similarity score.
- Review the borderline matches and reconcile the rest.
Step 1: Standardise Vendor Names
Normalise both datasets before comparing them. Lowercasing, collapsing whitespace, stripping punctuation and expanding common abbreviations make
Acme Corp.
and
acme corporation
comparable.
In Flookup Data Wrangler, the
Standardize Data
function handles this in one pass with the
punctuations
operation. Apply it to the vendor name column in your AP list and in your master table so both sides are cleaned with the same rules.
Step 2: Remove Duplicate Vendors
An AP list often contains the same supplier multiple times, either as a genuine repeat or as a duplicate created by an earlier import. If you match every row, one vendor can flood your review sheet with near-identical rows.
The Smart Deduplicate function detects fuzzy duplicate groups on the standardised name column and keeps one representative per group. Run it before matching so each unique vendor is matched exactly once.
Step 3: Fuzzy-Match Against the Master Table
With clean, deduplicated names on both sides, use
Fuzzy Match Data
to find the closest vendor in your master table for every AP record. Set the match threshold around
0.82
and ask the function to return both the best match and its similarity score, so every match carries an audit trail.
A score of 1.0 means the names are effectively identical after standardisation. Scores above 0.85 are usually safe to accept automatically. Scores between 0.6 and 0.85 are borderline and should be reviewed. Below 0.6 the records are probably different vendors.
Step 4: Review the Matches and Act
Export the matched pairs with their scores and work through three buckets:
- High confidence - accept as-is and update your master vendor table with any missing aliases.
- Medium confidence - eyeball the pair; fix standardisation rules if the same miss repeats.
- Unmatched - decide whether these are genuinely new vendors to add to your master table.
Keeping this review output means next month's reconciliation starts from a cleaner baselineand every matched pair stays auditable for finance.
Run It as a One-Click Template
If you do not want to chain those steps by hand, the AP Vendor Reconciliation workflow template in Flookup Data Wrangler runs the entire pipeline for you. It standardises vendor names, removes internal duplicates and fuzzy-matches your AP list against the master vendor table, then writes a review sheet with the source name, best match, match score, status and confidence for every record.
The template is part of the Data Nova plan. It needs only your AP list range, your master vendor table range and the column numbers that hold the vendor names.
Frequently Asked Questions
What is vendor reconciliation in Google Sheets?
Vendor reconciliation is the process of matching records in an accounts payable (AP) list against a master vendor table to confirm every supplier is known, correctly named and free of duplicates. In Google Sheets this is done by standardising vendor names and fuzzy matching the two lists so minor spelling differences do not hide a real match.
Why does vendor reconciliation fail with exact matching?
Exact matching requires both lists to spell every vendor identically. In practice AP lists contain abbreviations, punctuation differences, legal suffixes and typos such as 'Acme Corp' versus 'Acme Corporation'. Fuzzy matching compares similarity rather than equality, so it catches those variants and still reports a confidence score for each match.
How do I reconcile vendors without formulas?
The AP Vendor Reconciliation workflow template in Flookup Data Wrangler automates the whole process. It standardises vendor names, removes internal duplicates, fuzzy-matches the AP list against your master vendor table and writes a review sheet with the best match, match score, status and confidence for every record. It is a Data Nova feature.
What is a good match threshold for vendor names?
A threshold between 0.8 and 0.85 is a common starting point for vendor names. The AP Vendor Reconciliation template defaults to 0.82. Raise it if you see false positives, lower it if valid vendors are being left unmatchedand use the confidence labels on the review sheet to focus manual review on borderline matches.