To match and reconcile two spreadsheets with AI, define what one row means in each file, clean and test the matching keys, perform exact joins first, and use AI only to propose candidates for the remaining records. Classify every row as matched, left-only, right-only, or conflicting, then prove the result with counts, control totals, evidence, and human approval.

Key Takeaways:

Decide which source is authoritative for each field before comparing values.

Reject duplicate keys and many-to-many joins before they multiply records.

Exact matches are evidence; fuzzy scores are candidates that still need review.

Reconcile the full population, including unmatched and unresolved records.

The process uses the same bounded pattern as the general AI workflow: preserve source truth, constrain the model's role, execute repeatably, and review exceptions.

What must you define to match and reconcile two spreadsheets with AI?

Start with purpose. Are you checking invoices against payments, comparing two contact lists, finding inventory differences, validating a migration, or linking records that use different identifiers? The purpose determines the row grain, acceptable tolerances, authoritative fields, and consequences of a false match.

For each file, record:

source owner and capture date;
worksheet and table name;
what one row represents;
primary or candidate keys;
which fields are authoritative;
currency, timezone, date, and decimal conventions;
expected population and control totals;
known exclusions and retention rules.

Do not assume that the newer file is authoritative. A payment system may own settlement amount while an order system owns customer and product details. Authority can be field-specific.

Write a reconciliation contract

Question	Example answer
Row grain in A	one issued invoice
Row grain in B	one settled payment line
Exact key	invoice ID plus legal entity
Allowed relationship	one invoice to one settlement
Amount tolerance	exact in transaction currency
Date tolerance	three calendar days, review required
A-only meaning	invoice not found in settlement file
B-only meaning	settlement line not found in invoice file
Conflict owner	finance operations

This contract prevents the AI from quietly redefining “match” to maximize the matched percentage.

How do you prepare spreadsheet keys for reconciliation?

Preserve both source workbooks and work on derived tables. Profile row counts, distinct key counts, null keys, duplicate groups, data types, date ranges, and important totals. If either file is messy, apply the spreadsheet cleaning workflow first and keep its rule log.

Create normalized helper columns without replacing original values. Approved operations may include trimming known whitespace, normalizing an identifier's case, preserving leading zeros, standardizing a declared date format, or mapping an authoritative legal-entity code. Never strip punctuation from all identifiers merely because it improves match rate.

Test keys before joining:

Count blank keys on both sides.
Count distinct and duplicate keys.
List duplicate groups with source row IDs.
Confirm the allowed relationship: one-to-one, one-to-many, or many-to-one.
Reject unexpected many-to-many groups.
Compare key length and pattern distributions.
Sample keys from the beginning, middle, and end of each population.

A many-to-many join can produce a Cartesian multiplication. Two rows on side A and three on side B become six joined rows, corrupting counts and totals even when every source row looked valid.

Keep a normalized-key table

Source	Source row ID	Original key	Normalized key	Rule	Key status
A	A-1042	`inv-0017`	`INV-0017`	trim and uppercase approved ID	valid
B	B-881	`INV-0017`	`INV-0017`	unchanged	valid
B	B-882	blank	blank	no inference	exception

The original key remains visible. If a normalization rule creates a collision, stop and review the affected group.

How do you perform exact spreadsheet matching first?

Load both tables into a repeatable query, formula model, database, or script. Power Query can merge two queries using one or more common columns and supports join types that expose matches and unmatched rows.［1］ Use a full outer approach or an equivalent set of joins when the goal is complete reconciliation.

Create four primary result populations:

Status	Definition	Required evidence
matched	approved keys correspond and compared fields agree	both source row IDs and match key
left_only	A record has no approved B match	A row ID and attempted exact key
right_only	B record has no approved A match	B row ID and attempted exact key
conflict	approved keys correspond but controlled fields differ	both values, tolerance, and owner

Do not use an inner join alone. It shows successful matches while making unmatched records disappear. Likewise, do not label a record matched merely because one field is equal if the contract requires a composite key.

Compare fields under explicit tolerances

Create one comparison rule per field. Text may require exact equality after approved normalization. Amounts may allow a documented rounding tolerance. Dates may allow a settlement window. Categories may use a versioned crosswalk.

Record both values, the difference, the rule, and the result. A tolerance explains a difference; it does not erase it. Review boundary values and test units carefully. A difference of 0.01 has different meaning in currencies with different minor units, and a date difference can cross a month-end reporting boundary.

Reconcile counts and totals after the exact join

For side A, prove that matched + left_only + conflict accounts for every in-scope source row under the relationship rules. Do the corresponding check for side B. Recalculate amount totals by status and compare them with the frozen source controls.

If one-to-many relationships are legitimate, reconcile at both row level and business-object level. For example, several payment lines may settle one invoice; the line count differs, but grouped amounts and the invoice population should still reconcile.

When should you use AI or fuzzy matching?

Use fuzzy matching only after exact matching has exhausted approved keys and only on the remaining population. Microsoft describes Power Query fuzzy matching as a way to compare text values by similarity and exposes threshold and transformation options.［2］ That makes it useful for candidate generation, not automatic proof of identity.

Before fuzzy matching, define blocking rules that limit comparisons to plausible pairs. Examples include the same country, postal prefix, legal entity, month, or first character of a normalized name. Blocking reduces false positives and prevents every row from being compared with every other row.

Ask AI to propose candidate pairs with reasons:

For each unmatched A record, rank at most three B candidates that satisfy the blocking rules. Show the compared fields, exact differences, similarity signals, contradictory evidence, and missing evidence. Do not mark a pair as matched. Write no candidate when the evidence is insufficient.

AI can help explain abbreviations, reordered tokens, transliteration, or likely typographical errors, but it must not invent identifiers or fill missing evidence. Similar names do not prove that two customers, suppliers, or transactions are the same entity.

Use a human review table

A record ID	B record ID	Match key	Method	Score	Differences	Status	Evidence	Reviewer
A-1042	B-881	INV-0017	exact	n/a	none	accepted	composite key	OP
A-1049	B-907	name + postal block	fuzzy candidate	0.88	suffix and street abbreviation	review	no shared external ID	pending
A-1051	none	account ID	exact search	n/a	missing on B	left_only	source query	OP

The reviewer should accept, reject, or defer each candidate and add evidence. Never convert a threshold such as 0.85 into blanket approval without measured false-positive and false-negative performance on labeled examples.

OpenRefine's reconciliation workflow similarly distinguishes candidate matches from confirmed matches and supports reviewing or reconciling values against an external service.［3］ The broader lesson is that a candidate state must remain distinct from a confirmed identity.

How do you resolve conflicts without hiding them?

Route conflicts by type: amount, date, status, missing key, duplicate key, ownership, or unsupported mapping. Assign an owner and due date according to the business process. Preserve the original values and record any correction in the system that actually owns the field.

Do not “fix” the reconciliation output and leave the source systems unchanged. Otherwise the next run recreates the difference. A resolution should state the authoritative source, evidence, action, approver, and whether the change has been applied upstream.

Use statuses such as:

accepted_exact;
accepted_reviewed;
rejected_candidate;
left_only;
right_only;
conflict_open;
conflict_resolved;
out_of_scope.

Avoid a generic matched flag that cannot distinguish exact evidence from a subjective review.

NIST's generative AI profile highlights confabulation, information integrity, privacy, and human oversight risks.［4］ For reconciliation, those risks translate into source-bound prompts, minimal data sharing, an exception queue, and a human decision before any fuzzy candidate changes a record or triggers an external action.

What should the final reconciliation report contain?

Deliver a report that another person can reproduce:

purpose, scope, source versions, and owners;
row grain, authority, keys, normalization, and tolerances;
source counts and control totals;
exact-match, left-only, right-only, and conflict counts;
fuzzy candidate population and reviewer decisions;
unresolved exceptions and owners;
before-and-after controls;
query or script version and run timestamp;
approvals and upstream corrections.

Test reruns. The same source versions and rules should produce the same output. A new source version should either pass the schema contract or stop with a clear error. Silent column renames, type coercion, or missing worksheets should fail closed.

For disputes between narrative sources rather than tabular records, use the conflicting-source comparison method. For claims generated during reconciliation, follow the AI fact-checking workflow.

What common reconciliation mistakes should you avoid?

Joining before checking duplicate keys

Unexpected duplicates create many-to-many multiplication. Profile both sides and stop on relationships the contract does not allow.

Maximizing the match rate

A high percentage can hide false matches. Optimize for correct, explainable decisions and visible exceptions instead.

Auto-accepting fuzzy scores

Similarity is not identity. Use scores to rank candidates and require evidence-based review.

Hiding unmatched records with an inner join

Unmatched populations are part of the answer. Use full-population controls and separate left-only and right-only outputs.

Correcting the report but not the authoritative source

Local edits disappear on the next run. Resolve the issue in the owning system or record why it remains an exception.

Summary

Define row grain, field authority, keys, relationships, and tolerances.
Clean source values through approved helper columns while preserving originals.
Test null, duplicate, and many-to-many keys before joining.
Perform exact matching and expose both unmatched populations.
Use fuzzy and AI methods only to rank unresolved candidates.
Require human evidence for every non-exact acceptance.
Reconcile counts, amounts, exceptions, and upstream resolutions.
Preserve the rules, query, report, and approval trail for reruns.

FAQ

What is the best key for matching two spreadsheets?

Use a stable identifier shared by both sources, often with an additional scope field such as legal entity. Names and addresses are weaker and usually belong in reviewed candidate matching.

How do I find rows that exist in only one spreadsheet?

Use a full outer join or paired anti-joins on the approved key. Keep separate left-only and right-only tables and reconcile both to source row counts.

Can AI match customer names with spelling differences?

It can propose candidates, especially after blocking by other fields, but spelling similarity does not establish identity. Require corroborating evidence and human review.

What fuzzy match threshold should I use?

There is no universal safe threshold. Measure candidate quality on labeled examples, inspect results around the boundary, and keep scores as ranking signals rather than automatic approval.

How do I prevent duplicate rows after a merge?

Profile key cardinality on both sides before joining. Reject unexpected duplicate groups and many-to-many relationships, or aggregate under an approved business rule first.

What should happen to unmatched records?

Keep them in explicit left-only or right-only queues with source IDs, attempted keys, owners, and dispositions. Never delete them merely to make totals align.

How do I reconcile amounts that differ slightly?

Define currency-aware rounding or timing tolerances before comparison. Show both values and the difference, and route values outside the tolerance to conflict review.

What proves that a reconciliation is complete?

Every in-scope source record must appear in an allowed result population, counts and control totals must tie back to both sources, and unresolved exceptions must have visible owners and statuses.

Sources:

Microsoft Support - Merge queries (Power Query) — https://support.microsoft.com/en-US/Excel/merge-queries-power-query
Microsoft Support - Create a fuzzy match (Power Query) — https://support.microsoft.com/en-us/excel/create-a-fuzzy-match-power-query
OpenRefine - Reconciling — https://openrefine.org/docs/manual/reconciling/
NIST - Artificial Intelligence Risk Management Framework: Generative Artificial Intelligence Profile — https://www.nist.gov/publications/artificial-intelligence-risk-management-framework-generative-artificial-intelligence

Sources checked 29 August 2026.

Related articles:

How to Match and Reconcile Two Spreadsheets with AI