Check References Between Excel Sheets with pandas

Workbooks that grow by hand end up as small databases: an Orders sheet whose Customer ID column refers to the Customers sheet, a SKU column that should exist on the Products sheet. Excel does not enforce any of those links. A VLOOKUP silently returns #N/A for an order whose customer was mistyped, or the wrong customer when the lookup sheet has the same ID twice. Before a workbook like this feeds a report or a database load, check every reference the way a database would check a foreign key. This page, part of Validating Excel Data with Python, builds those checks with pandas and turns the failures into a list of Excel rows to fix.

Prerequisites

pip install pandas openpyxl

sales_book.xlsx has three sheets: eight orders, five customer rows (one ID listed twice, one customer closed) and five products. Some orders use keys with the wrong case or a trailing space, one refers to a customer that does not exist, one to an unknown SKU, and one has no customer at all.

Step 1 — Load the sheets and keep Excel row numbers

Read every sheet in one call and add each row's Excel row number before any filtering, so every error can point at a cell:

import pandas as pd

book = pd.read_excel("sales_book.xlsx", sheet_name=None, dtype=str)
for name, df in book.items():
    df.insert(0, "excel_row", df.index + 2)
orders, customers, products = book["Orders"], book["Customers"], book["Products"]
print({name: len(df) for name, df in book.items()})

dtype=str keeps every key as text on both sides; a key read as a number on one sheet and as text on the other never matches. sheet_name=None returns a dict of all sheets. The + 2 accounts for the header row and Excel counting from 1.

Step 2 — Check the lookup sheets have unique keys

A reference can only be checked against a lookup sheet whose keys are unique. If a customer ID appears twice, an order pointing at it is ambiguous — Excel's VLOOKUP takes the first row, a pandas merge takes both and duplicates the order. Check that first:

def duplicate_keys(df, key):
    dupes = df[df[key].duplicated(keep=False)]
    return dupes[["excel_row", key]].assign(problem=f"duplicate {key}")

print(duplicate_keys(customers, "Customer ID").to_string(index=False))
print(len(duplicate_keys(products, "SKU")), "duplicate SKUs")

C002 appears on rows 3 and 6 with different names — "Brightside" and "Brightside Group". That is a data problem to fix on the Customers sheet, not something a script should resolve by picking one; keep=False lists both rows so the person fixing it sees the conflict.

Step 3 — Find orders that point at nothing

With unique lookup keys, a reference check is one isin. Normalise both sides the same way first — strip spaces and upper-case — so the check catches real orphans rather than formatting differences:

def norm(s):
    return s.str.strip().str.upper()

def orphans(child, key, parent, parent_key=None):
    parent_keys = set(norm(parent[parent_key or key]).dropna())
    missing = child[key].isna()
    unknown = ~norm(child[key]).isin(parent_keys) & ~missing
    near_miss = norm(child[key]).isin(parent_keys) & ~child[key].isin(parent[parent_key or key])
    out = pd.concat([
        child[missing].assign(problem=f"{key} is blank"),
        child[unknown].assign(problem=f"{key} not found"),
        child[near_miss].assign(problem=f"{key} differs only in case or spaces"),
    ])
    return out[["excel_row", "Order", key, "problem"]].sort_values("excel_row")

print(orphans(orders, "Customer ID", customers).to_string(index=False))
print(orphans(orders, "SKU", products).to_string(index=False))

The checks sort the problems into three kinds, which need different fixes. C009 and X-999 do not exist at all — someone mistyped or the master sheet is missing a row. c001 and a-200 match once normalised; a lookup in Excel would still fail on them, so they are reported but the fix is simple. And SO-1008 has no customer. Treating near-misses as their own category stops them from hiding among the real orphans, and stops a normalising script from silently "fixing" data that other tools will read raw.

Step 4 — Check the reference is still valid

Existing is not always enough. An order for a closed customer, or a product that has been discontinued, refers to a row that exists but should not be used. Merge the orders onto the lookup sheet and test the looked-up attribute — after removing the duplicate keys, so the merge cannot multiply rows:

lookup = customers.drop_duplicates("Customer ID", keep=False)
joined = orders.assign(key=norm(orders["Customer ID"])).merge(
    lookup.assign(key=norm(lookup["Customer ID"]))[["key", "Status"]],
    on="key", how="left", validate="many_to_one")
closed = joined[joined["Status"].eq("closed")]
print(closed[["excel_row", "Order", "Customer ID", "Status"]].to_string(index=False))

validate="many_to_one" makes pandas raise if the lookup still had a duplicate key — a guard worth keeping in any lookup merge; Validate a Merge to Avoid Duplicate Rows explains the options. SO-1005 belongs to the closed customer C003. The orders for C002 get no status here, because C002 was excluded as ambiguous; they will pass once the Customers sheet is fixed.

Step 5 — Flag master rows nothing uses

The reverse check is informational rather than an error, but useful when tidying a workbook: customers and products that no order refers to. They may be new, retired, or duplicates under a slightly different ID:

used = set(norm(orders["SKU"]).dropna())
unused = products[~norm(products["SKU"]).isin(used)]
print(unused[["excel_row", "SKU", "Description"]].to_string(index=False))
used_customers = set(norm(orders["Customer ID"]).dropna())
idle = customers[~norm(customers["Customer ID"]).isin(used_customers)]
print(len(idle), "customers without orders")

The Latch, D-500 on row 6 of the Products sheet, has never been ordered. Every customer has at least one order — including C003, whose only order is the one to a closed account. Keep this list out of the error report, or give it its own sheet headed "for information", so that a clean workbook is not reported as failing because a new product has not sold yet.

Step 6 — Write one report for the whole workbook

Gather every check into a single sheet with the sheet name and row, so one file tells the owner everything to fix:

problems = pd.concat([
    duplicate_keys(customers, "Customer ID").assign(sheet="Customers").rename(columns={"Customer ID": "value"}),
    orphans(orders, "Customer ID", customers).assign(sheet="Orders").rename(columns={"Customer ID": "value"}),
    orphans(orders, "SKU", products).assign(sheet="Orders").rename(columns={"SKU": "value"}),
    closed.assign(sheet="Orders", problem="customer is closed").rename(columns={"Customer ID": "value"}),
])[["sheet", "excel_row", "value", "problem"]].sort_values(["sheet", "excel_row"])
problems.to_excel("reference_check.xlsx", index=False)
print(problems.to_string(index=False))
print(len(problems), "problems in", problems.groupby("sheet").excel_row.nunique().to_dict(), "rows")

Run the check as a gate

When the workbook feeds a load or a report, make the check stop the pipeline: exit with a non-zero status when problems is not empty, after writing the report, so a scheduler or CI job marks the run as failed and nobody works from a report built on broken references. The equivalent check inside Excel is a helper column of =COUNTIF(Customers!A:A, B2)=0 — useful for the sheet's owner, but easy to forget to copy down, which is why the script-based check is the one to trust.

References across workbooks

The same checks work when the lookup lives in another file — a customer master maintained by another team, say. Read it with its own pd.read_excel, keep its file name alongside the row numbers in the report, and run the checks unchanged. When a reference depends on two columns — a price list keyed by SKU and region — build the key from both columns on each side (norm(df["SKU"]) + "|" + norm(df["Region"])) and check that combined key. Merge Excel Data on Multiple Key Columns with pandas covers composite keys in merges.

Common pitfalls

• Mixed key types. Read keys with dtype=str on every sheet.
• Skipping the uniqueness check. Duplicate lookup keys make every downstream lookup ambiguous.
• Silently normalising. Report near-misses; tools that read the raw sheet still fail on them.
• Losing row numbers. Add excel_row before filtering or merging.
• Merging without validate. Use many_to_one so duplicate keys raise instead of multiplying rows.

Performance notes

isin against a set is fast for hundreds of thousands of rows. Normalise each key column once and reuse it rather than calling norm in every check when the sheets are large.

Conclusion

Load every sheet with keys as text and keep the Excel row number. Check each lookup sheet for duplicate keys first, then test every reference with isin on normalised keys, separating missing, not-found and near-miss references. Merge with validate="many_to_one" to check attributes such as a closed status, flag master rows nothing uses, and collect everything into one report that names the sheet and row to fix.

Frequently asked questions

How do I find rows whose ID is missing from another sheet? Load both sheets and test the key column with isin: orders~orders"Customer ID".isin(customers"Customer ID") returns every order whose customer is not on the Customers sheet.

Why do IDs that look identical not match? Usually a trailing space, a case difference or a type difference — "c001 " versus "C001", or the number 1001 versus the text "1001". Normalise both sides the same way (strip, upper-case, one type) before comparing, and report which rows only matched after normalising.

Why check the lookup sheet for duplicate keys? A VLOOKUP returns the first match, and a pandas merge returns every match. If a customer ID appears twice with different names, reports built on the sheet disagree depending on which tool did the lookup. Duplicate keys in a lookup sheet should be an error.

How do I report the Excel row number of a broken reference? When the header is in row 1, the Excel row is the DataFrame index plus 2. Add it as a column before filtering so it survives the checks.

Related

• Up one level: Validating Excel Data with Python — checking data before it spreads.
• Find Duplicate Rows in Excel with Python — duplicates in one sheet.
• VLOOKUP Equivalent in pandas for Excel Files — the lookup these checks protect.
• Validate Excel Rows with pydantic Models — rules within a row.