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

The VLOOKUP Equivalent in pandas for Excel Files

merge 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.

VLOOKUP is the function that made spreadsheets a database, and translating it is usually the first thing anyone does when moving a report to pandas. The translation is not one function but three, because VLOOKUP quietly does three different jobs: exact-match lookup, single-value translation, and — with its fourth argument set to TRUE — a banded approximate match that most people have used without noticing.

This guide covers all three, plus the two things pandas makes visible that Excel hides: which rows failed to match, and what happens when the lookup table has duplicate keys. It is part of Merging and Joining Excel DataFrames.

Three jobs VLOOKUP does, and the pandas call for each An exact-match lookup that returns several columns becomes merge with how left. A single key-to-value translation becomes map over a dictionary or Series. An approximate match, VLOOKUP's fourth argument set to TRUE, becomes merge_asof, which joins on the nearest key at or below the value. One spreadsheet function, three different operations exact match, many columns VLOOKUP(A2, tbl, 3, FALSE) df.merge(lookup, how="left") brings across every column you name, in one pass one value from a key VLOOKUP(A2, tbl, 2, FALSE) df["k"].map(mapping) faster, and cannot multiply the rows nearest match below VLOOKUP(A2, tbl, 2, TRUE) pd.merge_asof(...) rate bands, tiers, most recent reading

Prerequisites

Bash
pip install pandas openpyxl

Two workbooks: the transactions you are enriching and the lookup table you are enriching them from. The examples create both.

Step 1: Build the two tables

Python
import pandas as pd

orders = pd.DataFrame({
    "order_id": [1, 2, 3, 4, 5],
    "product_code": ["A-100", "B-220", "A-100", "Z-999", "C-310"],
    "quantity": [2, 1, 5, 3, 4],
})

products = pd.DataFrame({
    "product_code": ["A-100", "B-220", "C-310", "D-400"],
    "product_name": ["Widget", "Gadget", "Sprocket", "Flange"],
    "unit_price": [19.99, 45.00, 7.25, 12.10],
    "category": ["Core", "Core", "Accessory", "Accessory"],
})

orders.to_excel("orders.xlsx", index=False)
products.to_excel("products.xlsx", index=False)

Z-999 is deliberately absent from the lookup table — it is the row that would produce #N/A in a spreadsheet, and the one this guide cares about most.

Step 2: The direct translation — merge

=VLOOKUP(B2, products!A:D, 2, FALSE) becomes a left merge, and unlike VLOOKUP it can bring several columns at once:

Python
orders = pd.read_excel("orders.xlsx", dtype={"product_code": str})
products = pd.read_excel("products.xlsx", dtype={"product_code": str})

enriched = orders.merge(
    products[["product_code", "product_name", "unit_price", "category"]],
    on="product_code",
    how="left",              # keep every order, matched or not
    validate="m:1",          # many orders per product, one row per product
)
enriched["line_total"] = enriched["quantity"] * enriched["unit_price"]
print(enriched)

Three arguments carry the meaning. how="left" keeps every order whether or not it matched — the equivalent of VLOOKUP leaving #N/A rather than dropping the row. on="product_code" is the lookup key. And validate="m:1" is the guard with no spreadsheet equivalent: it asserts that the lookup table has one row per key, and raises immediately if it does not.

Reading both key columns with dtype=str prevents the classic silent failure — one file storing 00123 as text and the other as a number, so nothing matches and every row comes back empty.

Step 3: Find the rows that did not match

In Excel you scan for #N/A. In pandas you ask directly:

Python
checked = orders.merge(products, on="product_code", how="left", indicator=True)

missing = checked[checked["_merge"] == "left_only"]
if not missing.empty:
    print(f"{len(missing)} order(s) with no product record:")
    print(missing[["order_id", "product_code"]].to_string(index=False))
# 1 order(s) with no product record:
#  order_id product_code
#         4        Z-999

indicator=True adds a _merge column whose values are both, left_only or right_only. That single column turns "some rows have #N/A somewhere" into a list of exactly which orders reference an unknown product — which is the report someone can act on.

In a scheduled job, decide deliberately what a miss means. A handful of unmatched rows in a million might be acceptable and worth logging; a fifth of the file failing to match usually means the two exports are from different periods, and the job should stop rather than publish a report with a fifth of its revenue missing. That check belongs with the rest of the validation work:

Python
match_rate = (checked["_merge"] == "both").mean()
if match_rate < 0.95:
    raise ValueError(f"only {match_rate:.1%} of orders matched a product")

Step 4: Use map for a single-value lookup

When you only need one field, map is shorter, faster and structurally incapable of duplicating rows:

Python
price_by_code = products.set_index("product_code")["unit_price"]

orders["unit_price"] = orders["product_code"].map(price_by_code)
orders["category"] = orders["product_code"].map(
    products.set_index("product_code")["category"]).fillna("Unknown")

map accepts a dictionary or a Series indexed by the key, returns NaN for anything unmatched, and — crucially — always returns exactly as many values as it received. A merge against a lookup table with an accidental duplicate key silently produces extra rows; map cannot, which makes it the safer choice when you genuinely need one column.

fillna("Unknown") is the equivalent of wrapping the whole thing in IFERROR, and it is worth being deliberate about: filling a missing price with zero would quietly understate a total, while filling a missing category label is harmless.

What a duplicate lookup key does to a merge VLOOKUP returns the first match and hides the duplicate entirely. A merge returns one output row per matching pair, so a lookup table with the same key twice doubles those orders and inflates every total downstream. Passing validate m to 1 makes pandas raise instead of silently multiplying the rows. VLOOKUP: first match wins A-100 → 19.99 A-100 → 24.50 the second row is never seen row count unchanged, and the disagreement stays hidden merge: one row per pair order 1 → 19.99 order 1 → 24.50 one order became two rows every total downstream is now overstated validate="m:1" turns the silent inflation into an immediate error

Step 5: Handle duplicate keys deliberately

If validate="m:1" raises, the lookup table has the same key more than once. Find them and decide what they mean before working around them:

Python
dupes = products[products["product_code"].duplicated(keep=False)]
if not dupes.empty:
    print(dupes.sort_values("product_code").to_string(index=False))

# Then choose ONE of these, deliberately:
latest = (products.sort_values("valid_from")
                  .drop_duplicates("product_code", keep="last"))     # newest wins
# or aggregate, when several rows are all legitimate:
averaged = products.groupby("product_code", as_index=False)["unit_price"].mean()

The reason to look first is that duplicates usually mean something: two price versions with different effective dates, a product listed under two categories, or an export that ran twice. Silently taking the first — which is what VLOOKUP does — hides whichever of those it is. Find Duplicate Rows in Excel with Python covers reporting them back to whoever owns the data.

Step 6: The approximate match — merge_asof

VLOOKUP(value, table, 2, TRUE) finds the largest key at or below the value, which is how rate bands, commission tiers and postage brackets are built. In pandas that is merge_asof:

Python
bands = pd.DataFrame({
    "threshold": [0, 1_000, 5_000, 20_000],
    "rate": [0.00, 0.02, 0.035, 0.05],
})

deals = pd.DataFrame({"deal_id": [1, 2, 3, 4],
                      "value": [450, 3_200, 18_000, 92_000]})

banded = pd.merge_asof(
    deals.sort_values("value"),
    bands.sort_values("threshold"),
    left_on="value", right_on="threshold",
    direction="backward",            # the largest threshold <= value
)
print(banded[["deal_id", "value", "rate"]])
#    deal_id  value   rate
# 0        1    450  0.000
# 1        2   3200  0.020
# 2        3  18000  0.035
# 3        4  92000  0.050
How a backward merge_asof assigns each deal to a band The thresholds zero, one thousand, five thousand and twenty thousand divide the number line into four bands with rates of nought, two, three and a half and five percent. Each deal is matched to the largest threshold at or below its value, so a deal of three thousand two hundred falls in the one-thousand band and takes the two percent rate. direction="backward" — the largest threshold at or below the value 0 to 999 rate 0.0% 1,000 to 4,999 rate 2.0% 5,000 to 19,999 rate 3.5% 20,000 and above rate 5.0% 450 deal 1 3,200 deal 2 18,000 deal 3 92,000 deal 4 A deal never falls between bands — every value maps to exactly one row of the rate table

Both frames must be sorted on the join key or merge_asof raises — that requirement is the same one that makes VLOOKUP's approximate mode return nonsense on an unsorted table, except pandas tells you instead of guessing. direction="backward" is the VLOOKUP-TRUE behaviour; "forward" and "nearest" have no spreadsheet equivalent and are genuinely useful for matching a reading to the next scheduled time or the closest one either way.

Common pitfalls and gotchas

SymptomCauseFix
Nothing matches at allKey is text in one file, numeric in the otherRead both with dtype=str
Row count grew after the mergeDuplicate keys in the lookupvalidate="m:1", then de-duplicate deliberately
Rows disappearedhow="inner" (the default)Use how="left"
Whitespace stops a matchTrailing spaces from the export.str.strip() both keys first
Case-sensitive missesA-100 versus a-100Normalise case on both sides
_x and _y suffixes appearBoth frames have a column of that namesuffixes=, or select the columns you want first
merge_asof raises about orderingFrames not sorted on the keySort both before merging
Totals silently lowMissing prices filled with 0Fill labels, never fill money

Performance and scale notes

map over a Series is the fastest option and allocates the least, so use it for single-column lookups on large frames. merge is a hash join and comfortably handles millions of rows, but it materialises the result — a many-to-many merge on a large frame is the usual cause of a report suddenly needing gigabytes.

When the same lookup table is used repeatedly, build the mapping once outside the loop rather than calling set_index on every iteration. And when the lookup lives in a database rather than a workbook, consider doing the join in SQL instead of transferring the whole table to pandas — Moving Data Between Excel and Databases covers where to draw that line.

Conclusion

merge(how="left") is the general VLOOKUP replacement and brings across as many columns as you need; map is the faster answer when you need exactly one and cannot accidentally multiply rows; merge_asof is the banded lookup VLOOKUP's fourth argument was doing all along. Read the key columns as strings so a type mismatch cannot silently break every match, pass indicator=True to get the unmatched rows as data rather than as #N/A, and let validate="m:1" fail loudly on the duplicate key that would otherwise inflate every total in the report.

Frequently asked questions

Is merge or map the closer equivalent to VLOOKUP?merge with how="left" is the general answer and handles several returned columns. map is closer in spirit for a single key-to-value translation and is faster, but it only returns one column.

How do I find the rows that did not match? Pass indicator=True to merge and filter on _merge == "left_only". That is the equivalent of scanning for #N/A, except it gives you the rows rather than a marker.

Why did my row count grow after the merge? The lookup table has duplicate keys, so each source row matched several. De-duplicate the lookup first, or use validate="m:1" to make pandas raise instead.

What replaces VLOOKUP's TRUE fourth argument?pd.merge_asof, which joins on the nearest key at or below the value — the banded lookup used for rate tables and for matching a reading to the most recent timestamp.

Up to the parent guide:

Related guides: