Find tax bracket using approximate match (VLOOKUP)
HR needs to compute the withholding tax rate for each employee's annual taxable income. The reference table in columns A–B lists income thresholds in ascending order (0, $30,000, $60,000, $100,000) and their corresponding tax rates. Fill the Tax rate cells F2:F4 using VLOOKUP with approximate match so each employee is assigned the rate matching their income tier in E2:E4.
Your solution should use cell references so it updates automatically when Test Case input values change.
Notes
VLOOKUP's 4th parameter (range_lookup) controls the match mode. Passing TRUE (or omitting it) enables approximate match. In approximate mode, the lookup table MUST be sorted in ascending order. VLOOKUP scans down until it finds the largest value that is less than or equal to lookup_value. For example, $52,000 is greater than $30,000 but less than $60,000, so it matches the $30,000 row (15%).
Function guide
FAQ
When should I use approximate match (TRUE) in VLOOKUP?
Use approximate match when looking up numbers in ranges or tiers, such as tax brackets, shipping rates, volume discounts, or exam letter grades. Never use TRUE for exact text lookups like SKU, email, or employee ID.
Why must the lookup table be sorted ascending for approximate match?
VLOOKUP uses a binary-like scan that assumes the first column is ordered from smallest to largest. If the table is unsorted, VLOOKUP will stop early or return completely unpredictable values.
What happens if lookup_value is smaller than the first tier?
If lookup_value is smaller than the minimum value in the first row (e.g. less than 0 in this table), VLOOKUP returns #N/A because no valid tier exists.