Approximate-Match VLOOKUP for Tiered Rates in pandas

Rate tables are everywhere in business spreadsheets: commission by sales band, discount by order size, shipping cost by weight, tax by income bracket. Excel handles them with VLOOKUP(value, table, 2, TRUE) — the "approximate match" that finds the largest threshold not above the value — or XLOOKUP with match mode -1. The pandas equivalent is merge_asof, a merge that matches each row to the nearest earlier key instead of an exact one. This page, part of Excel Formula Equivalents in pandas, reproduces the lookup exactly — verified against VLOOKUP recalculated in LibreOffice — and covers the progressive calculation that a lookup cannot do.

Prerequisites

pip install pandas openpyxl numpy

tiers.xlsx has four commission tiers starting at 0, 10,000, 25,000 and 50,000. sales.xlsx has seven reps whose sales sit on and around the thresholds — including a negative figure from a returns-heavy month.

Step 1 — merge_asof: the approximate-match merge

merge_asof matches each row of the left table to the last row of the right table whose key is less than or equal to it — direction="backward", which is the default and exactly VLOOKUP's TRUE:

import numpy as np
import pandas as pd

sales = pd.read_excel("sales.xlsx")
tiers = pd.read_excel("tiers.xlsx")

sales["sales"] = sales["sales"].astype(float)
tiers["threshold"] = tiers["threshold"].astype(float)

rated = pd.merge_asof(sales.sort_values("sales"), tiers.sort_values("threshold"),
                      left_on="sales", right_on="threshold", direction="backward")
print(rated.to_string(index=False))

Every rep gets the rate of the tier their sales fall into: 9,999.99 is still in the 2% tier, exactly 10,000 moves into 3%, 60,000 is in the top tier. The two astype(float) lines are not decoration. merge_asof requires both keys to have the same dtype, and the thresholds, read from whole numbers, arrive as integers while the sales arrive as floats — without the conversion, the merge stops with MergeError: incompatible merge keys. Both inputs must also be sorted by their keys; merge_asof raises rather than guess if they are not.

Step 2 — Values below the first tier

The rep with negative sales has no tier: nothing in the table is less than or equal to −500. merge_asof returns NaN for the rate, where VLOOKUP returns #N/A. Decide what it means — usually that the tier table should start at the lowest possible value — and handle it explicitly:

below = rated["rate"].isna()
print("no tier for:", rated.loc[below, ["rep", "sales"]].to_dict("records"))

rated["rate"] = rated["rate"].fillna(0.0)          # no commission on negative sales
rated["commission"] = (rated["sales"].clip(lower=0) * rated["rate"]).round(2)
print(rated[["rep", "sales", "rate", "commission"]].to_string(index=False))

Filling with a zero rate and clipping negative sales encodes a business rule — no negative commission — rather than hiding a lookup failure. An alternative is to add a first tier at -inf to the table, which makes the rule visible in the tier data itself.

Step 3 — searchsorted and pd.cut as alternatives

When only the rate is needed, two lighter tools give the same answer. np.searchsorted finds, for each value, the position at which it would be inserted into the sorted thresholds; subtracting one gives the tier row. pd.cut with left-closed intervals labels each value with its tier:

thresholds = tiers["threshold"].to_numpy()
pos = np.searchsorted(thresholds, sales["sales"].to_numpy(), side="right") - 1
sales["rate_ss"] = np.where(pos >= 0, tiers["rate"].to_numpy()[pos.clip(0)], np.nan)

sales["rate_cut"] = pd.cut(sales["sales"], bins=[*thresholds, np.inf], right=False,
                           labels=tiers["rate"].tolist(), ordered=False).astype(float)
print(sales[["rep", "sales", "rate_ss", "rate_cut"]].to_string(index=False))

side="right" is what makes a value exactly on a threshold belong to that threshold's tier, matching VLOOKUP; side="left" would push 10,000 back into the 2% tier. right=False does the same job for pd.cut. Both keep the original row order, which merge_asof does not — it returns rows in key order. Bin Values into Ranges Like Excel FREQUENCY with pd.cut explains the interval options.

Step 4 — Different tiers for different groups

Tier tables often vary by region, product line or year. merge_asof handles that with by, which matches on an exact key and an approximate one — VLOOKUP would need one table per region or a helper column:

regional = pd.DataFrame({
    "region": ["North"] * 3 + ["South"] * 3,
    "threshold": [0.0, 10000.0, 30000.0, 0.0, 20000.0, 40000.0],
    "rate": [0.02, 0.04, 0.06, 0.025, 0.035, 0.05],
}).sort_values("threshold")

by_region = pd.merge_asof(sales.sort_values("sales"), regional,
                          left_on="sales", right_on="threshold", by="region", direction="backward")
print(by_region[["rep", "region", "sales", "threshold", "rate"]].sort_values("rep").to_string(index=False))

Eve and Finn are both in their region's second tier, but different thresholds apply — 10,000 in the North, 20,000 in the South — and Dev's 10,000, which reached the second tier on the company table, is still in the South's first tier. Sort the tier table by the threshold only; merge_asof handles the grouping. Keep all tier tables in one sheet with a region column, so a new region is a few new rows rather than a new lookup.

Check the tier table before trusting it

Tier tables are maintained by hand, and a single mistake — a threshold typed twice, two rows out of order, a rate entered as 5 instead of 0.05 — changes every result without an error. Validate the table each run, before the lookup:

def check_tiers(table, key="threshold", value="rate"):
    problems = []
    if table[key].duplicated().any():
        problems.append("duplicate thresholds")
    if not table[key].is_monotonic_increasing:
        problems.append("thresholds not in ascending order")
    if not table[value].between(0, 1).all():
        problems.append("rates outside 0-1 (percent typed as a whole number?)")
    return problems

print(check_tiers(tiers))
print(check_tiers(pd.DataFrame({"threshold": [0, 25000, 10000], "rate": [0.02, 5, 0.03]})))

The real table passes; the broken one fails on order and on the rate typed as 5. Sorting the table in code would hide the first problem rather than fix it — a row out of order usually means a typo in a threshold — so fail and let the owner correct the sheet.

Step 5 — Progressive commission: rates per band, not per total

A tiered lookup gives one rate to the whole amount: 25,000 in sales earns 5% of 25,000. Many schemes are progressive instead — 2% on the first 10,000, 3% on the next 15,000, 5% above that — like income tax. That is not a lookup at all; it is a sum over the bands:

def progressive(amount, thresholds, rates):
    total = 0.0
    for i, (low, rate) in enumerate(zip(thresholds, rates)):
        high = thresholds[i + 1] if i + 1 < len(thresholds) else np.inf
        if amount > low:
            total += (min(amount, high) - low) * rate
    return round(total, 2)

t, r = tiers["threshold"].tolist(), tiers["rate"].tolist()
sales["flat_tier"] = (sales["sales"].clip(lower=0) * sales["rate_ss"].fillna(0)).round(2)
sales["progressive"] = sales["sales"].map(lambda a: progressive(a, t, r))
print(sales[["rep", "sales", "flat_tier", "progressive"]].to_string(index=False))

At 24,000 in sales, the flat tier pays 720 (3% of everything) and the progressive scheme 620 (2% of 10,000 plus 3% of 14,000). The flat scheme also has a cliff: at 25,000, commission jumps from 720 to 1,250 for one extra dollar of sales, which progressive schemes avoid. Which scheme applies is a contract question — check the plan document, not the spreadsheet — and the two must never be mixed up in code.

Common pitfalls

• Mismatched key dtypes. merge_asof needs identical types; convert both keys to float.
• Unsorted inputs. Sort both sides by their keys; merge_asof refuses otherwise, as VLOOKUP TRUE silently misbehaves on an unsorted table.
• Values below the first tier. They get NaN; decide the rule and encode it.
• side="left" in searchsorted. Values on a threshold then fall into the tier below.
• Confusing flat tiers with progressive bands. They pay different amounts; follow the plan document.

Performance notes

merge_asof and searchsorted are both binary searches in compiled code and handle millions of rows against thousands of tiers in well under a second. The progressive function runs in Python per row; for large volumes, vectorise it by computing each band's contribution with clip for all rows at once and summing the columns.

Conclusion

VLOOKUP(value, table, 2, TRUE) is merge_asof(..., direction="backward") in pandas, with both inputs sorted and both keys of the same dtype; np.searchsorted(side="right") - 1 and pd.cut(right=False) give the same answer when only the rate is needed. Decide explicitly what happens below the first tier, use by= for tier tables that vary by group, and compute progressive schemes as a sum over bands rather than as a lookup.

Frequently asked questions

What does VLOOKUP with TRUE actually return? The value from the row with the largest key that is less than or equal to the lookup value, in a table sorted ascending by key. A value below the first key returns #N/A.

What is the pandas equivalent? pd.merge_asof(left, tiers, left_on="sales", right_on="threshold", direction="backward"). Both sides must be sorted by their key and the keys must have the same dtype. np.searchsorted and pd.cut with right=False give the same answer.

Why does merge_asof raise MergeError about dtypes? It requires both keys to have exactly the same type. Integer thresholds read from one sheet and float sales from another are a common mismatch; convert both to float before merging.

How is a progressive commission different? A tiered lookup applies one rate to the whole amount. A progressive (marginal) scheme applies each rate only to the part of the amount inside its band, like income tax. It needs a sum over the bands, not a lookup.

Related

• Up one level: Excel Formula Equivalents in pandas — every function, translated.
• The VLOOKUP Equivalent in pandas for Excel Files — exact-match lookups.
• Match Rows by Nearest Date with pandas merge_asof — the same merge on dates.
• Build a Sales Commission Report with Python and Excel — tiers in a complete report.