XLOOKUP Equivalent in pandas

XLOOKUP replaced VLOOKUP and INDEX/MATCH for many Excel users because one function covers exact and approximate matches, a default for missing keys, and a choice between first and last match. When the workbook logic moves to Python, each of those arguments needs a pandas counterpart. This guide, part of Excel Formula Equivalents in pandas, goes through them one at a time and ends with an xlookup function you can drop into a project. The general lookup patterns — map, merge, composite keys, two-dimensional lookups — are covered in INDEX MATCH Equivalent in pandas; this page focuses on what is specific to XLOOKUP.

Prerequisites

pip install pandas openpyxl

A price list with a duplicated key and inconsistent case, and orders to price:

import fnmatch

import pandas as pd

prices = pd.DataFrame({
    "sku": ["A-100", "a-200", "B-300", "A-100"],
    "price": [9.5, 12.0, 30.0, 9.9],
    "supplier": ["North", "North", "South", "West"],
})
orders = pd.DataFrame({"order": [1, 2, 3, 4], "sku": ["A-100", "A-200", "C-999", "B-300"]})
with pd.ExcelWriter("lookup.xlsx") as writer:
    prices.to_excel(writer, sheet_name="Prices", index=False)
    orders.to_excel(writer, sheet_name="Orders", index=False)
prices = pd.read_excel("lookup.xlsx", sheet_name="Prices")
orders = pd.read_excel("lookup.xlsx", sheet_name="Orders")

Step 1 — Exact match with if_not_found

=XLOOKUP(B2, Prices!A:A, Prices!B:B, 0) returns the price or 0 when the SKU is not listed. In pandas, index the price column by SKU, map the order SKUs through it and fill the gaps:

lookup = prices.drop_duplicates("sku").set_index("sku")["price"]
orders["price"] = orders["sku"].map(lookup)
print(orders["price"].tolist())                  # [9.5, nan, nan, 30.0]
orders["price_or_0"] = orders["price"].fillna(0)

Keep the unfilled column until you have reported the misses: a default of 0 makes an unknown SKU look like a free item. Two orders missed here — C-999, which is genuinely absent, and A-200, which is in the list as "a-200". Excel would have found that one.

Step 2 — Excel's case-insensitive matching

XLOOKUP ignores case: "A-200" matches "a-200". pandas compares strings exactly. To get Excel's result, casefold the key on both sides:

lookup = (prices.assign(key=prices["sku"].str.casefold())
                .drop_duplicates("key")
                .set_index("key")["price"])
orders["price"] = orders["sku"].str.casefold().map(lookup)
print(orders["price"].tolist())                  # [9.5, 12.0, nan, 30.0]

casefold is a stronger form of lower that also handles characters such as the German "ß". If the price list has trailing spaces or non-breaking spaces as well — common in hand-maintained sheets — clean those too; see Strip Whitespace and Normalise Text Columns with pandas.

Step 3 — First or last match with search_mode

A-100 appears twice in the price list, at 9.5 and 9.9. XLOOKUP's default search_mode of 1 returns the first; -1 searches from the bottom and returns the last — the usual choice when newer rows are appended below older ones. In pandas this is simply which duplicate you keep:

first = prices.drop_duplicates("sku", keep="first").set_index("sku")["price"]
last = prices.drop_duplicates("sku", keep="last").set_index("sku")["price"]
print(first["A-100"], last["A-100"])             # 9.5 9.9

Do not skip this step: set_index on a column with duplicates, followed by map, raises InvalidIndexError: Reindexing only valid with uniquely valued Index objects. Whether duplicates should exist at all is a question for the owner of the price list; count them and report them before choosing one.

Step 4 — Next smaller or next larger with match_mode

Discount tiers, tax bands and shipping rates are looked up with match_mode -1 (exact match or the next smaller item) or 1 (exact match or the next larger item). merge_asof does both, with direction="backward" and "forward":

tiers = pd.DataFrame({"min_qty": [1, 10, 50, 100], "discount": [0.0, 0.05, 0.10, 0.15]})
qty = pd.DataFrame({"line": [1, 2, 3, 4, 5], "qty": [75, 5, 250, 10, 0]})

matched = pd.merge_asof(qty.sort_values("qty"), tiers,
                        left_on="qty", right_on="min_qty", direction="backward")
matched = matched.sort_values("line").reset_index(drop=True)
print(matched[["line", "qty", "discount"]].values.tolist())
# [[1.0, 75.0, 0.1], [2.0, 5.0, 0.0], [3.0, 250.0, 0.15], [4.0, 10.0, 0.05], [5.0, 0.0, nan]]

Both sides must be sorted by the key, so sort the left table, merge, and sort back by an order column to restore the original row order. A quantity of 0 is below the first tier, so it has no next smaller item — Excel returns #N/A there, and pandas returns NaN. XLOOKUP does not need the lookup column sorted for these modes, but merge_asof does, which is why the tier table is kept in ascending order.

Step 5 — Wildcards with match_mode 2

With match_mode 2, XLOOKUP accepts * for any characters and ? for one character. Python's fnmatch uses the same symbols. Casefold both sides to keep Excel's case-insensitivity:

def wildcard_first(pattern, keys, values):
    pat = pattern.casefold()
    for key, value in zip(keys, values):
        if fnmatch.fnmatchcase(str(key).casefold(), pat):
            return value
    return None

print(wildcard_first("b*", prices["sku"], prices["price"]))       # 30.0
print(wildcard_first("A-?00", prices["sku"], prices["price"]))    # 9.5

Excel escapes a literal asterisk as ~*; fnmatch uses [*] instead. Wildcard lookups scan the list row by row, which is fine for a few hundred patterns. For many thousands, translate the patterns to regular expressions and use str.contains or str.match on the whole column.

Step 6 — Return several columns at once

XLOOKUP can return a whole row — =XLOOKUP(B2, Prices!A:A, Prices!B:C) spills price and supplier. In pandas that is a left merge on the prepared key, selecting the columns to bring across:

table = (prices.assign(key=prices["sku"].str.casefold())
               .drop_duplicates("key", keep="last")[["key", "price", "supplier"]])
result = orders[["order", "sku"]].assign(key=orders["sku"].str.casefold()) \
    .merge(table, on="key", how="left", validate="many_to_one").drop(columns="key")
print(result.values.tolist())

validate="many_to_one" makes pandas raise an error if the lookup side still has duplicate keys, which would otherwise multiply order rows silently. Merge validation is covered in more depth in Validate a Merge to Avoid Duplicate Rows.

A reusable xlookup function

Put the pieces together so a translated workbook reads close to the original formulas:

def xlookup(values, keys, returns, if_not_found=None, search_mode=1, ignore_case=True):
    keys = pd.Series(keys).reset_index(drop=True)
    returns = pd.Series(returns).reset_index(drop=True)
    values = pd.Series(values)
    norm = (lambda s: s.astype(str).str.casefold()) if ignore_case else (lambda s: s)
    table = pd.DataFrame({"k": norm(keys), "r": returns})
    table = table.drop_duplicates("k", keep="first" if search_mode == 1 else "last")
    out = norm(values).map(table.set_index("k")["r"])
    return out if if_not_found is None else out.fillna(if_not_found)

orders["price"] = xlookup(orders["sku"], prices["sku"], prices["price"], if_not_found=0, search_mode=-1)
print(orders["price"].tolist())                  # [9.9, 12.0, 0.0, 30.0]

The defaults match Excel's: case-insensitive, first match, no default. Because every argument is named, a reviewer comparing the script with the old workbook can check each translated formula argument by argument. Approximate and wildcard modes stay separate functions, because they need sorted input and row-by-row matching respectively.

Why not translate the formula row by row?

The most literal translation of a column full of XLOOKUP formulas is a function applied to every row, each one scanning the price list for its SKU. It gives the right answer and is very slow. On a test with 200,000 order lines and 20,000 SKUs, map finished in under a tenth of a second, while a row-by-row lambda that filtered the price list for each order took about 40 seconds. Excel hides this cost because it recalculates cells incrementally; a script pays it every run.

Build the lookup Series or table once, outside any loop, and apply it to the whole column. That also makes the lookup table a single object you can inspect, test and report on, instead of logic spread across thousands of cells.

Common pitfalls

• Numbers stored as text. A key of 100 never matches "100", in Excel or in pandas. Convert both sides to one type first.
• Duplicate keys in the lookup table. map refuses them and merge multiplies rows. Choose first or last explicitly.
• Whole-column references in the original. Prices!A:A in Excel includes the header and blank rows. When you translate, read the table with a proper header and drop empty keys, or a blank lookup value can match a blank row.
• Dates as keys. A date cell read as a timestamp never equals the text "2026-01-05". Convert both sides with pd.to_datetime first.
• A default that hides errors. if_not_found=0 turns missing prices into free items. Report misses before filling.

Conclusion

XLOOKUP's arguments map cleanly onto pandas: map with fillna for exact lookups with a default, casefolded keys for Excel's case-insensitivity, drop_duplicates(keep=…) for search mode, merge_asof for next smaller or larger, fnmatch for wildcards and a validated left merge to return several columns. Wrap the common case in one xlookup function and keep the unusual modes explicit.

Frequently asked questions

What is the pandas equivalent of XLOOKUP? For a single returned column, Series.map with a lookup Series indexed by the key; for several returned columns, DataFrame.merge with how="left". Approximate matches use merge_asof.

Is XLOOKUP case-sensitive? No. XLOOKUP treats "a-200" and "A-200" as the same key, while pandas comparisons are case-sensitive. Casefold both sides of the lookup to reproduce Excel's behaviour.

How do I return the last match instead of the first? Drop duplicate keys from the lookup table with keep="last" before mapping. That matches XLOOKUP with search_mode -1, which searches from the bottom up.

What replaces the if_not_found argument? Call fillna on the result with the default value. Keep the unfilled result as well if you need to report which keys were missing.

Related

• Up one level: Excel Formula Equivalents in pandas — Excel functions translated to pandas.
• INDEX MATCH Equivalent in pandas — the general lookup patterns.
• SUMIF and SUMIFS Equivalent in pandas — conditional totals.
• Match Rows by Nearest Date with merge_asof — approximate matching on dates.