|

Reconcile Two Spreadsheets: 3 Reliable Methods

Bydelmich

Compare lists, find differences, and join data safely without breaking keys.

Method 1 — Lookup Match (VLOOKUP/XLOOKUP)

Goal: pull a column from B into A by matching a key.

Sheets:

=VLOOKUP(A2, SheetB!A:B, 2, false)

Excel (modern):

=XLOOKUP(A2, SheetB!A:A, SheetB!B:B, "not found", 0)

Method 2 — INDEX‑MATCH (robust classic)

=INDEX(SheetB!B:B, MATCH(A2, SheetB!A:A, 0))

Add error handling:

=IFERROR(INDEX(SheetB!B:B, MATCH(A2, SheetB!A:A, 0)), "")

Method 3 — QUERY Join (Sheets)

Left join A (left) to B (right) by id:

=QUERY({A!A:D,B!B:B},
"select Col1,Col2,Col3,Col4,Col6 where Col1 is not null",1)

(Col6 is B’s value aligned by row after preparing matching order.)

Highlight differences (conditional formatting)

  • Rule: =ISNA(MATCH(A2, SheetB!A:A, 0)) → red fill (not in B)
  • Rule on B: =ISNA(MATCH(B2, SheetA!A:A, 0)) → blue fill (not in A)

Reconciliation Checklist

  • Normalize case: =LOWER(TRIM(A2))
  • Strip whitespace/hidden chars: =TRIM(CLEAN(A2))
  • Ensure keys are unique; if not, use a composite key: =A2&"|"&B2

Download: Reconciliation Workbook

Includes all three methods + sample data and prebuilt CF rules.

FAQ

Which is fastest? XLOOKUP is simpler; INDEX‑MATCH is portable; QUERY is powerful for joins.

What breaks matches? Mismatched types (text vs number), stray spaces, inconsistent case.

Similar Posts