Python Excel AutomationPractical guides for developers
Start learning
Guide
Advanced Data Transformation And CleaningDeep dive

INDEX MATCH Equivalent in pandas

INDEX/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.

INDEX/MATCH exists because VLOOKUP cannot look leftwards. pandas has no such limitation, so the pair collapses into either merge or map depending on how much you need back — and the awkward part of the Excel version, keeping the two ranges aligned, simply disappears. This guide, part of Excel Formula Equivalents in pandas, covers both forms, composite keys, and the approximate-match case that merge_asof handles.

One column back, or several Mapping from a Series keyed by the lookup column adds exactly one column and cannot change the row count, while merge brings several columns at once and can multiply rows if the key repeats. map one column back row count is safe takes a dict too shortest form merge many columns at once composite keys join type is explicit validate the key choose by how much you need back decides the tool

Prerequisites

Bash
pip install pandas openpyxl
Python
import pandas as pd

orders = pd.DataFrame({
    "Order": [1001, 1002, 1003, 1004, 1005],
    "SKU": ["A-100", "B-200", "A-100", "C-300", "D-400"],
    "Region": ["North", "South", "West", "North", "South"],
    "Units": [12, 4, 9, 22, 7],
})

products = pd.DataFrame({
    "SKU": ["A-100", "B-200", "C-300"],
    "Description": ["Widget, small", "Gadget", "Widget, large"],
    "Unit_Price": [19.99, 49.50, 34.75],
    "Category": ["Widgets", "Gadgets", "Widgets"],
})

The one-column form: map

When the lookup returns a single column and the key is a single column, map is the shortest possible translation and it cannot change the shape of your data.

Python
# =INDEX(products!C:C, MATCH(B2, products!A:A, 0))
prices = products.set_index("SKU")["Unit_Price"]
orders["Unit_Price"] = orders["SKU"].map(prices)

orders["Line_Total"] = orders["Units"] * orders["Unit_Price"]
print(orders)

set_index("SKU")["Unit_Price"] produces a Series indexed by SKU — which is exactly what a lookup table is. map then translates each SKU into its price, leaving NaN where the SKU is unknown. The D-400 row demonstrates that: no product record, so no price, and the line total is NaN rather than a silently wrong number.

map also accepts a plain dictionary, which is often the clearest form when the mapping is short and belongs in the code rather than in a file:

Python
region_owner = {"North": "Ana", "South": "Ben", "West": "Dev"}
orders["Owner"] = orders["Region"].map(region_owner)

The many-column form: merge

When the lookup should bring back several columns — which in Excel means one INDEX/MATCH per column, each re-scanning the table — merge does it in one call.

Python
# Three INDEX/MATCH formulas, replaced by one join
enriched = orders.merge(
    products[["SKU", "Description", "Unit_Price", "Category"]],
    on="SKU",
    how="left",
    validate="m:1",
)
print(enriched)

how="left" gives VLOOKUP's semantics: every order survives, unmatched ones get NaN. validate="m:1" is the argument worth adopting as a habit — it asserts that the lookup table has at most one row per key, and raises immediately if it does not. Without it, a duplicated key in the lookup table silently multiplies rows, and the total at the bottom of the report is quietly too large. That failure is described in full in VLOOKUP Equivalent in pandas for Excel Files.

Reporting what did not match

Excel wraps the formula in IFERROR and shows a blank. That hides the problem one cell at a time; pandas lets you count it once.

Python
unmatched = enriched.loc[enriched["Description"].isna(), ["Order", "SKU"]]
if not unmatched.empty:
    print(f"{len(unmatched)} order(s) reference an unknown SKU:")
    print(unmatched.to_string(index=False))

An unmatched key is nearly always a data problem — a product retired without updating the catalogue, a typo in an export, a trailing space — and it is worth reporting rather than filling. When a default genuinely is correct, make it explicit rather than incidental:

Python
enriched["Category"] = enriched["Category"].fillna("Uncategorised")

Composite keys

Matching on two columns without a helper column Excel needs a concatenated helper column or an array formula to match on two keys, while merge takes a list of column names and compares them as a tuple. composite keys two key columns Region and Category on=[...] compared as a pair joined rows no helper column concatenating keys by hand can collide; a column list cannot

Matching on two columns is where the spreadsheet version gets ugly: Excel needs a helper column concatenating the keys, or an array formula. merge takes a list.

Python
targets = pd.DataFrame({
    "Region": ["North", "North", "South", "South"],
    "Category": ["Widgets", "Gadgets", "Widgets", "Gadgets"],
    "Target": [40000.0, 15000.0, 22000.0, 30000.0],
})

with_targets = enriched.merge(targets, on=["Region", "Category"], how="left")
print(with_targets[["Order", "Region", "Category", "Target"]])

No helper column, no concatenation, and no risk that "North" + "Widgets" collides with "NorthWid" + "gets" — which is a real failure mode of the concatenation trick when keys have variable length.

When the two frames name the same concept differently, left_on and right_on avoid renaming anything:

Python
merged = orders.merge(
    products.rename(columns={"SKU": "Item_Code"}),
    left_on="SKU", right_on="Item_Code", how="left",
).drop(columns="Item_Code")

Approximate matches: merge_asof

MATCH with a match type of 1 finds the largest value less than or equal to the lookup — the mechanism behind every rate table, tax band and volume-discount tier. merge_asof is the direct equivalent and it handles the sorting requirement explicitly.

Python
discounts = pd.DataFrame({
    "Min_Units": [0, 10, 20],
    "Discount": [0.0, 0.05, 0.12],
})

priced = pd.merge_asof(
    orders.sort_values("Units"),
    discounts.sort_values("Min_Units"),
    left_on="Units",
    right_on="Min_Units",
    direction="backward",
)
print(priced[["Order", "Units", "Min_Units", "Discount"]])

Both frames must be sorted on the join key or the result is wrong rather than an error — the same requirement Excel's approximate MATCH imposes, and the same silent failure when it is not met. direction="backward" is the default and matches Excel's behaviour; "forward" and "nearest" have no spreadsheet equivalent at all.

Two-dimensional lookups

The other classic INDEX/MATCH shape uses two MATCH calls — one for the row, one for the column — to pick a value out of a rectangular grid: a rate by region and month, a price by size and finish. That layout is a pivot table stored as a sheet, and the pandas answer is to unpivot it back into rows before joining.

Python
grid = pd.DataFrame({
    "Region": ["North", "South", "West"],
    "Jan": [0.10, 0.08, 0.06],
    "Feb": [0.11, 0.08, 0.07],
    "Mar": [0.12, 0.09, 0.07],
})

rates = grid.melt(id_vars="Region", var_name="Month", value_name="Rate")
print(rates.head())

orders["Month"] = ["Jan", "Feb", "Feb", "Mar", "Mar"]
with_rate = orders.merge(rates, on=["Region", "Month"], how="left")

melt turns the wide grid into one row per Region-Month pair, after which the two-dimensional lookup is an ordinary composite-key join. That reshaping step is worth doing even when a direct lookup would work, because the long form is the shape every other pandas operation expects — and it survives a new month being added to the grid, which a formula referencing a fixed column range does not. Unpivot a Wide Excel Sheet with pandas melt covers the reshaping in detail.

Keeping the lookup table honest

A lookup is only as reliable as the table behind it, and two checks catch most of what goes wrong. The first is uniqueness of the key, which validate="m:1" enforces at merge time. The second is coverage — whether every key in the data has a row in the table — which nothing enforces unless you ask.

Python
def check_lookup(frame, table, key):
    duplicated = table[key].duplicated().sum()
    uncovered = sorted(set(frame[key].dropna()) - set(table[key]))
    if duplicated:
        raise ValueError(f"lookup table has {duplicated} duplicate {key} value(s)")
    if uncovered:
        print(f"warning: {len(uncovered)} unmatched {key}(s): {uncovered[:10]}")

check_lookup(orders, products, "SKU")

Running that before the merge rather than inspecting NaN afterwards means the message names the problem in the reference data rather than describing its symptom in the output. On a scheduled job it is also the difference between an alert that says "the product catalogue is stale" and a report that quietly shows blanks in the description column.

Common pitfalls

SymptomCauseFix
Row count grows after a mergeDuplicate keys in the lookup tablevalidate="m:1", and de-duplicate the lookup
Everything is NaNKey dtypes differ — text in one frame, integer in the otherRead both with the same dtype, or cast before merging
Some keys match, most do notWhitespace or case differences.str.strip().str.casefold() on both sides
merge_asof gives wrong bandsOne or both frames unsortedSort both on the join key first
Columns come back as Price_x and Price_yBoth frames have a column of that nameSelect the columns you need before merging, or pass suffixes
map returns all NaNThe Series index is not the key columnset_index(key)[value] before mapping

Performance and scale

Resolving 50,000 lookups against a 5,000-row table Excel's exact MATCH scans the lookup range for each row, while pandas builds a hash index of the table once and resolves every key in constant time afterwards. MATCH, exact scan per row merge hash built once map from a Series hash built once relative cost the lookup table is indexed once, not once per row

The performance story is lopsided. Excel's exact MATCH scans the lookup range for every row, so a 50,000-row sheet against a 5,000-row table performs 250 million comparisons in the worst case. pandas builds a hash index of the lookup table once and then resolves each key in constant time.

Python
import time

start = time.perf_counter()
result = orders.merge(products, on="SKU", how="left")
print(f"merge: {time.perf_counter() - start:.4f}s for {len(result):,} rows")

Two habits keep it fast on large data. Select only the columns you need from the lookup table before merging, so the join does not carry twenty unused columns through the result. And convert repeated string keys to category dtype when both sides share the same categories — the join then compares integer codes.

The one case that is genuinely slower in pandas is merge_asof on unsorted data, because the sort dominates. If the same lookup runs repeatedly, sort the reference table once and keep it sorted.

Conclusion

INDEX/MATCH becomes map for a single column and merge for several, and the leftward-lookup problem that motivated the formula pair stops existing. Use validate="m:1" so a duplicated key raises instead of inflating the report, count the unmatched rows rather than hiding them behind a default, pass a list for composite keys, and reach for merge_asof when the match is approximate.

Frequently asked questions

When should I use map instead of merge? Use map when you want one column added from a lookup table keyed by a single column — it is shorter and cannot duplicate rows. Use merge when you need several columns, a composite key, or control over the join type.

How do I reproduce MATCH on its own? MATCH returns a position, which is rarely what you want in pandas. If you genuinely need the row number, use df.index.get_indexer or a reset_index followed by a merge; usually the position was only ever a means to fetch a value.

What replaces an approximate MATCH with match type 1? pd.merge_asof, which joins each row to the nearest preceding key. It is the right tool for banded lookups — rate tables, tax bands, tier thresholds — and it requires both frames sorted on the key.

Why did my row count go up after a merge? The lookup table has more than one row per key, so each source row matched several. Check with other'Key'.duplicated().any() before merging, and pass validate='m:1' to make pandas raise instead of silently multiplying rows.

How do I handle a lookup that finds nothing? A left merge leaves NaN where Excel would show #N/A. Count them straight afterwards rather than filling them — an unmatched key usually means a data problem worth reporting, not a blank worth hiding.