Column A has bracket floors 0, 10000, 40000. Column B has rates. Look up the rate for income in E2

公式

=VLOOKUP(E2, A2:B10, 2, TRUE)

说明

TRUE (approximate) requires column A to be sorted ascending. VLOOKUP finds the last floor that is still ≤ E2 — the standard tax-bracket pattern.

步骤

  1. Sort bracket floors smallest to largest.
  2. TRUE enables approximate match.
  3. The returned row is the greatest floor ≤ income.

变体

XLOOKUP next-smaller

Match mode -1 is next-smaller. Brackets do not have to sit in the leftmost column.

=XLOOKUP(E2, A2:A10, B2:B10, "n/a", -1)

INDEX / MATCH equivalent

Match type 1 is the approximate equivalent of VLOOKUP TRUE.

=INDEX(B2:B10, MATCH(E2, A2:A10, 1))

Exact match reminder

Use FALSE for IDs. Approximate match on unsorted IDs returns wrong rows silently.

=VLOOKUP(E2, A2:B10, 2, FALSE)