Cleaning & transforming data
Joins, lookups & comparisons
VLOOKUP and INDEX MATCH in pandas, merging files, fuzzy matching and diffing two workbooks.
Start with the full guides
The long-form treatment of joins, lookups & comparisons, with the reasoning the shorter pages assume.
Step-by-step how-tos
One task each, with code you can copy, run and adapt.
- How-toAppend New Rows to a Master Excel File Without DuplicatesWeekly extracts overlap — the same invoices appear in this week's file and last week's — and appending them blindly fills the master workbook with duplicates. Find genuinely new rows with a key-based anti-join, decide what to do with rows that changed, append with openpyxl so the master's formatting and table survive, keep reruns idempotent, and log every run.
- How-toConcatenate Excel Sheets with Different ColumnsStack monthly exports whose columns drift — align headers, map renamed fields, keep a source column, and report what each file was missing before you combine them.
- How-toCreate Every Combination of Two Lists with a Cross Join in pandasBuild every store × product, employee × training course or region × month combination with merge(how="cross"), then left-join the actual data so missing combinations show as zero or "not done" instead of disappearing. Covers three or more lists, MultiIndex.from_product, filtering impossible pairs, guarding against size explosions, and writing a complete grid to Excel.
- How-toFind Rows in One Excel File Missing from AnotherReconcile two spreadsheets in Python — an indicator merge for both directions, composite keys, near-matches from whitespace, and a formatted exceptions workbook.
- How-toFuzzy Match Names Between Two Excel Files with PythonJoin two spreadsheets when the names do not quite match — Acme Ltd vs ACME Limited — with rapidfuzz: normalise names, choose a scorer, set a cut-off, score every pair at once, and send doubtful matches for review.
- How-toHandle Many-to-Many Lookups Without Duplicating RowsWhen a lookup key matches several rows — a product in two promotions, a customer with several account managers — a pandas merge multiplies the left rows and every total built on them is inflated. Detect the fan-out, then choose deliberately: collapse the lookup to one row per key, pick one match by a rule, combine matches into a list, or allocate amounts across them so totals still reconcile.
- How-toJoin Rows to Effective Date Ranges with pandasLook up the price, rate or rule that was in force on each row's date: a price list with valid-from and valid-to dates joined to orders, open-ended ranges, the merge-and-filter method, merge_asof with a validity check, orders that fall in a gap, overlapping price periods that match twice, and a check that every order got exactly one price.
- How-toLook Up All Matches for a Key Instead of the FirstVLOOKUP and XLOOKUP stop at the first match, so a customer with five orders shows one. Return every match with pandas instead: all matching values joined into one cell like TEXTJOIN with FILTER, a count and a total alongside, the nth match, each match in its own column, and the one-to-many merge that keeps a row per match — with the spreadsheet's first-match result shown for comparison.
- How-toMatch Rows by Nearest Date with pandas merge_asofAttach the exchange rate, price or status that applied at each transaction's date — the latest one on or before it — with pandas merge_asof. Match within groups such as currency, limit how stale a match may be, choose backward, forward or nearest, and fix the sorting and dtype errors it raises.
- How-toMerge Excel Data on Multiple Key Columns with pandasMatch rows on region and product, or store and date, rather than on one ID. Merge on several columns at once, find why some rows do not match — a key typed as 7 in one file and "007" in another, a trailing space — guard against duplicate keys multiplying rows, and fall back to a less specific key when the exact combination is missing.
- How-toMerge Excel Files with Different Column Names in pandasTwo systems export the same customers under different headers — "Customer No" and "cust_id", "Email" and "email_address" — with keys formatted differently. Map both files to one schema, normalise the keys, merge on several columns and combine the overlapping fields with a clear rule for which source wins.
- How-toMerge More Than Two Excel Files on a Key ColumnCombine several workbooks that describe the same records — HR, payroll, IT and training exports keyed by employee ID — into one wide table: chain merges with functools.reduce, keep keys that appear in only some files, avoid _x/_y column collisions, record which files each key came from, catch duplicate keys before they multiply rows, and write the result with a coverage summary.
- How-toMerge on Keys That Differ in Case and Spacing with pandasA merge that should match every row matches half of them because one file says "SKU-001 " and the other "sku-001", or an ID has lost its leading zeros, or an invisible non-breaking space hides in a cell. Build a normalised join key on both sides, merge on it while keeping the original values, detect keys that collapse into collisions, and report what still does not match.
- How-toMerge Two Excel Files on a Common Column in PythonMerge two Excel files on a shared column in Python with pandas: load both workbooks, normalize the key, join with pd.merge, handle different names and duplicate keys, export.
- How-toRoll Up Totals Through a Parent-Child HierarchySum amounts posted to the bottom of a cost-centre, account or product hierarchy up through every level with pandas: a parent-child table turned into each node's path to the top, totals for every ancestor in one join, depth and indented labels, amounts posted to non-leaf nodes, a check that the top equals the sum of all postings, and an indented Excel report with subtotals at every level.
- How-toUpdate One Excel File from Another with pandasApply a changes file to a master list — new prices, corrected emails, customers added — the way people do with copy-paste and VLOOKUP. Compare DataFrame.update, combine_first and an explicit upsert, keep a change log of old and new values, and write the updates into the existing workbook without destroying its formatting.
- How-toValidate a pandas Merge to Avoid Duplicate RowsA duplicate key in a lookup sheet makes a pandas merge silently add rows and inflate totals. Use merge's validate argument, indicator column and before-and-after checks to catch duplicate keys, unmatched rows and missing keys that match each other before a report goes out.
- How-toCompare Two Excel Files for Differences with PythonDiff two versions of a workbook with pandas — added, removed and changed rows keyed on an identifier, a cell-level change list, and a colour-coded difference report in Excel.
- How-toConvert Multi-Currency Transactions with an FX Rate TableReport transactions in several currencies in one base currency: turn euro-based reference rates into base-currency rates by triangulation, apply the rate in force on each transaction date, round to each currency's minor units, use monthly average rates for the P&L and closing rates for balances, and calculate the unrealised gain or loss on open invoices.
- How-toHighlight Differences Between Two Columns with openpyxlMark every row where system A and system B disagree, or where this month's value differs from last month's sheet. Write the comparison as a conditional format with openpyxl, and decide deliberately how case, blanks versus zero, trailing spaces and rounding differences should count.
- How-toMap Free-Text Job Titles to Standard CategoriesTurn hundreds of free-text job titles from an HR export or a contact list into a handful of standard job families and seniority levels with pandas: normalise the text, expand abbreviations, apply an ordered keyword rule table kept in Excel, fall back to fuzzy matching against known titles, separate seniority from function, and report every title that still needs a human decision.
- How-toStandardise Category Values with a Mapping Table in pandasTurn "N. America", "north america ", "NA" and "USA" into one region name using a mapping table kept in Excel, where the people who own the categories can maintain it. Normalise keys, check the table for conflicts, report unmapped values with counts and suggestions, and append new ones for review.
- How-toApproximate-Match VLOOKUP for Tiered Rates in pandasLook up commission rates, discount bands or tax brackets the way VLOOKUP(value, table, 2, TRUE) does — the largest threshold not above the value. Use merge_asof, searchsorted or pd.cut, handle values below the first tier, keep the tier table sorted, use different tiers per region, and calculate progressive (marginal) amounts correctly.
- How-toINDEX MATCH Equivalent in pandasINDEX/MATCH becomes map for one column and merge for several. Handle composite keys without a helper column, report unmatched keys, and use merge_asof for banded lookups.
- How-toXLOOKUP Equivalent in pandasReproduce every XLOOKUP argument in pandas — exact lookups with a default for missing keys, Excel's case-insensitive matching, first or last match, next smaller or next larger item, wildcards, and returning several columns at once — with a reusable xlookup function.
- How-toXLOOKUP with Multiple Criteria in pandasReproduce Excel lookups that match on two or more columns — XLOOKUP(1, (A=x)*(B=y), C) and its concatenated-key cousins — in pandas: an exact match on several keys with merge, a not-found default, guarding against duplicate keys, tiered "next smaller" matches by quantity with merge_asof, last-match lookups, and a check against the same XLOOKUP formulas recalculated in a workbook.
- How-toThe VLOOKUP Equivalent in pandas for Excel Filesmerge is the real answer, map is the fast one: translating a VLOOKUP into pandas, keeping the unmatched rows visible, handling duplicate lookup keys, and the approximate-match case VLOOKUP's fourth argument hides.
- How-toJoin and Pivot Excel Data with PolarsCombine and reshape workbook data with Polars: read several sheets, join them with validate to catch duplicate keys, find unmatched rows with an anti join, group and aggregate, pivot quarters into columns, unpivot back, run the pipeline lazily, and write a formatted result with write_excel — with the pandas equivalents alongside.
- How-toCheck References Between Excel Sheets with pandasFind orders that point at customers who do not exist, products missing from the price list and lookup sheets with duplicate keys. Check every reference from one sheet to another with pandas, normalise keys so near-misses are caught, report the Excel rows that break a reference, and flag master rows nothing uses.
- How-toBuild a Bank Reconciliation Report with Python and ExcelMatch a month of bank statement lines to the cash book: first on cheque numbers and invoice references, then on amount within a few days, one to one. List what is left — outstanding cheques, deposits in transit, bank charges and interest — flag old unmatched items, and prove the adjusted bank and book balances agree.
- How-toBuild a Bill of Materials Cost RollupCalculate the standard cost of every assembled product from a multi-level bill of materials in Excel: walk the parent–component tree recursively with quantities, add each assembly's own labour and overhead, detect loops and components with no cost, show where a part is used, and work out what a supplier price increase does to every finished product — all with pandas and plain Python.
- How-toBuild a Foreign Exchange Revaluation ReportRevalue open foreign-currency invoices and bank balances at the month-end rate with pandas: join each item to its booking rate and the closing rate, calculate the unrealised gain or loss per item and per currency, get the sign right for receivables, payables and cash, produce the revaluation journal and its reversal, and write a report that reconciles to the ledger.
- How-toBuild an Intercompany ReconciliationReconcile what one group company says another owes it against what the other company says it owes, with pandas: match intercompany transactions from both ledgers by reference, convert to a common currency, separate timing differences, exchange differences and genuine mismatches, check that balances eliminate on consolidation, and write a reconciliation both finance teams can work from.
- How-toCompare Supplier Quotes with PythonCompare supplier quotes that arrive as differently laid-out Excel files: map each supplier's columns to one schema, convert currencies and pack sizes to a per-unit price, apply quantity price breaks with merge_asof, add delivery and minimum-order rules, score price, lead time and payment terms with weights, and write a comparison matrix that highlights the best offer per item.
- How-toReconcile Payroll to the General LedgerProve each month that payroll was posted to the ledger correctly with pandas: check every payslip adds up, map the payroll register to the expense and liability accounts it should hit, compare with the general ledger journal by account and cost centre, tie net pay to the bank payment file, and produce a reconciliation workbook that explains every difference.
- How-toUpdate a Price List with Effective DatesApply a sheet of price changes to a price list that keeps history: close each current price the day before its replacement takes effect, add the new row, reject changes that are backdated into a closed period or that would overlap, check that every product has exactly one price on every day, look up any product's price on any date, and produce a change report showing old and new prices side by side.
- How-toDiff Excel Files in Git with a textconv DriverMake git diff show what changed inside a workbook instead of "Binary files differ": a small Python converter that prints every cell as sheet, address, value and formula, a .gitattributes entry and a diff driver that use it, readable diffs of templates and golden files in pull requests, and the limits of the approach.
- How-toWrite Lookup Formulas (XLOOKUP, VLOOKUP, INDEX/MATCH) with openpyxlGenerate live lookup formulas that pull prices, names or rates from another sheet: VLOOKUP with an exact match, INDEX/MATCH, and XLOOKUP — which must be written with the _xlfn prefix or it shows #NAME? — plus absolute ranges that survive copying, not-found handling, tiered approximate matches, a named lookup range, and a LibreOffice check that every formula returns what pandas expects.
Follow it through a track
Prefer an ordered route? These tracks cover joins, lookups & comparisons along the way, in the order that builds on itself.
Related topics
Subjects that keep turning up on the same pages as this one, most overlap first.
Joins, lookups & comparisons is part of Cleaning & transforming data.