XLOOKUP with Multiple Criteria in pandas

Single-key lookups are easy in both Excel and pandas. Real lookups often need more than one key: the price of a product in a region, the rate for a grade in a year, the discount for a customer at an order size. In Excel the usual answer is an array trick — XLOOKUP(1, (Prices[Product]=A2)*(Prices[Region]=B2), Prices[Price]) — or a helper column that glues the keys together. Both work, both are hard to read and both silently pick the first match if the price list has accidental duplicates. pandas expresses the same lookups more directly, and makes the duplicate problem visible. This page translates the common multi-criteria XLOOKUP patterns: an exact match on two keys with a not-found value, a duplicate-key check, a tiered "exact or next smaller" match by quantity within each product and region, and a last-match lookup — and checks the results against the same XLOOKUP formulas recalculated in a workbook. It belongs to Excel Formula Equivalents in pandas.

Prerequisites

pip install pandas openpyxl

LibreOffice (soffice, version 24.8 or later, which supports XLOOKUP) recalculates the comparison workbook in Step 4. The data is a price list by product, region and minimum order quantity, and a set of orders to price.

Step 1 — Exact match on two keys, with a default

import pandas as pd

prices = pd.DataFrame({
    "Product": ["Bolts", "Bolts", "Bolts", "Bolts", "Nuts", "Nuts", "Washers"],
    "Region": ["North", "North", "South", "South", "North", "South", "North"],
    "MinQty": [1, 500, 1, 500, 1, 1, 1],
    "Price": [0.45, 0.39, 0.48, 0.41, 0.12, 0.13, 0.05],
})
orders = pd.DataFrame({
    "Order": ["SO-1", "SO-2", "SO-3", "SO-4", "SO-5", "SO-6"],
    "Product": ["Bolts", "Bolts", "Nuts", "Washers", "Rivets", "Bolts"],
    "Region": ["North", "South", "South", "South", "North", "North"],
    "Qty": [120, 800, 300, 50, 10, 500],
})

base = prices[prices["MinQty"] == 1][["Product", "Region", "Price"]]
priced = orders.merge(base, on=["Product", "Region"], how="left", validate="many_to_one", indicator="Found")
priced["Found"] = priced["Found"].eq("both")
priced["Price"] = priced["Price"].fillna(0)                      # XLOOKUP's if_not_found argument
print(priced.to_string(index=False))

merge(on=["Product", "Region"]) is the multi-criteria lookup: a row matches only when both columns match, which is exactly what (Product=x)*(Region=y) means in the Excel array formula. how="left" keeps every order, matched or not, and fillna plays the role of XLOOKUP's if_not_found argument. The Found flag, from the merge's indicator option, records which rows matched, because a default of 0 is indistinguishable from a real price of 0 otherwise. Washers in the South and Rivets anywhere are not in the price list and come back unpriced. Key columns must match exactly, including case and stray spaces: "North " with a trailing space is a different region to pandas, just as it is to Excel, so clean both sides' keys first — trimming and consistent capitalisation — when the data comes from people typing.

Step 2 — Make duplicate keys an error

validate="many_to_one" checks that each Product–Region pair appears at most once in the lookup table, and raises if it does not:

dirty = pd.concat([base, pd.DataFrame({"Product": ["Nuts"], "Region": ["South"], "Price": [0.15]})])
try:
    orders.merge(dirty, on=["Product", "Region"], how="left", validate="many_to_one")
except pd.errors.MergeError as e:
    print("MergeError:", e)
print(dirty[dirty.duplicated(["Product", "Region"], keep=False)].to_string(index=False))

With a second price for Nuts in the South, Excel's XLOOKUP would quietly return the first one; an unvalidated merge would be worse, duplicating the order row so it is counted twice. The validation stops the lookup and the duplicated check shows the offending rows. A duplicate key in a price list is a data problem to fix at source, not something the lookup should choose between. More on this guard in Validate a Merge to Avoid Duplicate Rows.

Step 3 — Tiered prices: exact or next smaller, within each key

Price breaks are XLOOKUP's match mode −1, "exact match or next smaller": the price for 800 units is the row with the largest minimum quantity not above 800, for the same product and region. That is merge_asof with by:

tiered = pd.merge_asof(orders.sort_values("Qty"), prices.sort_values("MinQty"),
                       left_on="Qty", right_on="MinQty", by=["Product", "Region"], direction="backward")
tiered = tiered.sort_values("Order")[["Order", "Product", "Region", "Qty", "MinQty", "Price"]]
print(tiered.to_string(index=False))

merge_asof matches each order to the last price row whose MinQty is at or below the order's quantity, and by restricts the search to rows with the same product and region — the extra criteria. Both frames must be sorted on the matching column. SO-2's 800 South bolts get the 500+ price of 0.41, SO-6's exactly 500 North bolts get 0.39 (an exact match on the break), and SO-1's 120 North bolts the base 0.45. Unmatched combinations come back as NaN again.

Returning several columns, and why not apply

An Excel XLOOKUP can return a whole row of results — price, currency and unit — by giving a multi-column return array. A merge does that naturally: every column of the lookup table that is not a key comes along, so selecting ["Product", "Region", "Price", "Currency", "Unit"] before merging returns all three at once. Resist the temptation to write the lookup as a function applied row by row, filtering the price table for each order; it reads like the Excel formula but runs one filter per row, which on tens of thousands of orders is slower by orders of magnitude than a single merge, and it hides the duplicate-key problem because each filter just takes the first row. The merge also makes the result easy to audit: with indicator, every row says whether it was found, which the row-by-row version would need extra code to report.

Step 4 — Check against XLOOKUP in a workbook

Write the orders and prices to a workbook with the equivalent XLOOKUP formulas, recalculate, and compare:

import subprocess
from openpyxl import Workbook, load_workbook

wb = Workbook()
ws = wb.active
ws.title = "Orders"
pl = wb.create_sheet("Prices")
pl.append(list(prices.columns))
for row in prices.itertuples(index=False):
    pl.append(list(row))
ws.append(list(orders.columns) + ["Base price", "Tier price"])
n = len(prices) + 1
for r, row in enumerate(orders.itertuples(index=False), start=2):
    ws.append(list(row))
    ws[f"E{r}"] = (f"=_xlfn.XLOOKUP(1,(Prices!$A$2:$A${n}=B{r})*(Prices!$B$2:$B${n}=C{r})*(Prices!$C$2:$C${n}=1),"
                   f"Prices!$D$2:$D${n},0)")
    ws[f"F{r}"] = (f"=_xlfn.XLOOKUP(1,(Prices!$A$2:$A${n}=B{r})*(Prices!$B$2:$B${n}=C{r})*(Prices!$C$2:$C${n}<=D{r}),"
                   f"Prices!$D$2:$D${n},0,0,-1)")
wb.save("lookup_check.xlsx")
subprocess.run(["soffice", "--headless", "--convert-to", "xlsx", "--outdir", "calc", "lookup_check.xlsx"], check=True, capture_output=True)
calc = load_workbook("calc/lookup_check.xlsx", data_only=True)["Orders"]
excel = pd.DataFrame([[c.value for c in row] for row in calc.iter_rows(min_row=2, max_col=6)], columns=list(orders.columns) + ["Base", "Tier"])
compare = excel.assign(pandas_base=priced["Price"].values, pandas_tier=tiered.set_index("Order").loc[excel["Order"], "Price"].fillna(0).values)
print(compare[["Order", "Base", "pandas_base", "Tier", "pandas_tier"]].to_string(index=False))
print("all equal:", (compare["Base"] == compare["pandas_base"]).all() and (compare["Tier"] == compare["pandas_tier"]).all())

The base-price formula is the classic multi-criteria XLOOKUP: multiplying the three conditions gives 1 only where all are true, and XLOOKUP looks for that 1. The tier formula changes the quantity condition to <= and sets the search mode to −1, searching from the bottom so that the last qualifying row — the highest break — is found; this relies on the price list being sorted by minimum quantity, just as merge_asof does. Openpyxl needs the _xlfn. prefix for XLOOKUP, as covered in Fix #NAME? Errors for New Excel Functions Written by Python. Every order gets the same base and tier price from the workbook as from pandas — including SO-2, where the base lookup returns the South price of 0.48 and the tiered lookup the 500+ break of 0.41, and the two unpriced orders, which both methods return as 0 through the not-found argument.

Common pitfalls

• Concatenated helper keys. "Bolts|North" breaks when a value contains the separator; merge on the columns themselves.
• No duplicate check. Duplicate keys give silent first-match results in Excel and doubled order rows in pandas.
• Defaulting to 0 without a flag. Record which rows were actually found in the lookup table.
• Unsorted data for tiered matches. merge_asof and search mode −1 both need the price breaks sorted by quantity.
• Missing by in merge_asof. Without it, price breaks from other products and regions are used.

Conclusion

A multi-criteria XLOOKUP is a merge on several columns: merge(on=[...], how="left", validate="many_to_one") with fillna for the not-found value and a flag for which rows matched. Tiered "exact or next smaller" lookups are merge_asof with by for the exact-match keys and direction="backward" on the quantity, both sides sorted. Validate against the Excel formulas in a recalculated workbook when the two must agree.

Frequently asked questions

How do I do an XLOOKUP with two criteria in pandas? Merge the two DataFrames on both key columns with how="left", for example orders.merge(prices, on="Product", "Region", how="left").

How do I return a default value when there is no match? Fill the missing values after the merge with fillna, and keep a separate flag for rows that matched so defaults can be distinguished from real values.

How do I look up the next smaller value within groups? Use pd.merge_asof with by set to the exact-match columns, the numeric column as left_on and right_on, direction="backward", and both frames sorted on that column.

Why does my multi-criteria lookup duplicate rows? The lookup table has duplicate keys; pass validate="many_to_one" to raise an error and fix the duplicates at source.

Related

• Up one level: Excel Formula Equivalents in pandas — Excel functions in Python.
• XLOOKUP Equivalent in pandas — the single-key case.
• Merge Excel Data on Multiple Key Columns — multi-key merges in general.
• Match Rows by Nearest Date with merge_asof — merge_asof on dates.