# How to look up a value between two numbers in Excel

Put the lower bound of each band in a sorted column and use an approximate match: =XLOOKUP(value, lower_bounds, results,,-1).

**Excel:** `=XLOOKUP(E1,A2:A6,B2:B6,,-1)` (Excel 2021, 2024 and Microsoft 365)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D | E |
| --- | --- | --- | --- | --- |
| Sales from | Commission |  | Rep sales | 37500 |
| 0 | 0.02 |  | Commission rate |  |
| 10000 | 0.04 |  |  |  |
| 25000 | 0.06 |  |  |  |
| 50000 | 0.08 |  |  |  |
| 100000 | 0.1 |  |  |  |

Formula in E2: `=XLOOKUP(E1,A2:A6,B2:B6,,-1)`

Result: 6%

## How it works

Each row gives where a band starts. Match mode `-1` tells XLOOKUP to return the exact match or, if there isn't one, the next smaller value: 37,500 isn't in the list, so it uses the 25,000 band and returns 6%.

## Other ways

- `=VLOOKUP(E1,A2:B6,2,TRUE)`: The classic approximate match. TRUE (or leaving the argument out) needs the first column sorted from smallest to largest.
- `=INDEX(B2:B6,MATCH(E1,A2:A6,1))`: MATCH with 1 finds the largest bound that is less than or equal to the value. Same sorted-column rule as VLOOKUP.

## Common mistakes

- The band table must list the lower bound of each band, starting at the lowest possible value (0 here). A value below the first bound returns #N/A.
- VLOOKUP and MATCH with 1 need the bounds sorted ascending. XLOOKUP with match mode -1 doesn't.
- Nested IFs work too, but every band change means editing the formula. A table is easier to maintain.

## Questions

**How do I calculate tax with brackets?**

Look up the rate the same way, but progressive tax needs each slice taxed at its own rate; see a SUMPRODUCT bracket formula for that.

**What does match mode 1 do?**

It returns the exact match or the next larger value, for bands defined by their upper bound.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-29.

Source: https://spreadsheetpractice.com/formulas/lookup-value-between-two-numbers