VLOOKUP approximate match for tax brackets

How it works, step by step

1. 01B2 holds taxable income, for example 52,000.
2. 02VLOOKUP scans Brackets!$A$2:$A$5, the lower bounds, which must be sorted from smallest to largest.
3. 03TRUE means approximate match. VLOOKUP stops at the largest lower bound that does not exceed 52,000, which is 44,725.
4. 04The 2 returns the rate from the second column of that row: 22%.
5. 05Income from 0 to 10,999 lands on the 0 row and gets 10%. A value below the first bound, such as a negative number, returns #N/A.

The same logic in four tools

=VLOOKUP(B2,Brackets!$A$2:$B$5,2,TRUE)

=VLOOKUP(B2,Brackets!$A$2:$B$5,2,TRUE)

SELECT p.employee_id, p.taxable_income, b.rate
FROM payroll p
LEFT JOIN LATERAL (
  SELECT rate
  FROM brackets
  WHERE lower_bound <= p.taxable_income
  ORDER BY lower_bound DESC
  LIMIT 1
) b ON TRUE;

import pandas as pd

result = pd.merge_asof(
    payroll.sort_values("taxable_income"),
    brackets.sort_values("lower_bound"),
    left_on="taxable_income",
    right_on="lower_bound",
    direction="backward",
)

Mistakes that break it

• !The first column of the bracket table must be sorted ascending. If it is not, VLOOKUP with TRUE returns wrong rates with no error.
• !Leaving off the last argument also means TRUE. That default is what causes wrong results in exact-match lookups, so always write TRUE or FALSE explicitly.
• !This returns the marginal rate for the bracket, not the total tax owed. Progressive tax on the full amount needs the tax from each lower bracket added, usually through a cumulative-tax column in the table.
• !pd.merge_asof requires both DataFrames sorted on their keys and raises an error if the left key has NaN values. The result follows the sorted order of payroll, not its original row order.

Common questions

What does TRUE mean in VLOOKUP?

TRUE means approximate match. VLOOKUP returns the row with the largest value that is less than or equal to the lookup value, and it assumes the first column is sorted ascending.

How do I look up a tax bracket in Excel?

Put each bracket's lower bound in a sorted column with the rate beside it, then use =VLOOKUP(B2,Brackets!$A$2:$B$5,2,TRUE). In Excel 365, =XLOOKUP(B2,Brackets!A2:A5,Brackets!B2:B5,,-1) does the same without requiring the table to be sorted.

What is the pandas equivalent of VLOOKUP approximate match?

pd.merge_asof with direction="backward". It matches each row to the last key that is less than or equal to it, and both DataFrames must be sorted by their keys.