How to look up a value between two numbers in Excel
Formula (Excel and Google Sheets)
=XLOOKUP(E1,A2:A6,B2:B6,,-1)Put the lower bound of each band in a sorted column and use an approximate match: =XLOOKUP(value, lower_bounds, results,,-1).
- Result on the example
- 6% in
E2 - Works in
- Excel 2021, 2024 and Microsoft 365
- Google Sheets
- Same formula
See it work
The formula is already in E2. Change any input and the result updates, or edit the formula to experiment.
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%.
=XLOOKUP(E1,A2:A6,B2:B6,,-1)Result0.06
E1$37,500The value in E1.
A2:A65 rows × 1 col: $0, $10,000, $25,000, $50,000, …A range: 5 cells in one column.
B2:B65 rows × 1 col: 2%, 4%, 6%, 8%, …A range: 5 cells in one column.
-1-1Makes the value negative.
XLOOKUP(E1,A2:A6,B2:B6,,-1)0.06XLOOKUP: Searches one range and returns the matching item from another.- lookup_value
E1$37,500 - lookup_array
A2:A65 rows × 1 col: $0, $10,000, $25,000, $50,000, … - return_array
B2:B65 rows × 1 col: 2%, 4%, 6%, 8%, … - if_not_found
(omitted) - match_mode
-1-1
- lookup_value
In Google Sheets
The same formula works in Google Sheets: =XLOOKUP(E1,A2:A6,B2:B6,,-1). We ran it in Google Sheets mode on the same data and it returns 6%.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =VLOOKUP(E1,A2:B6,2,TRUE) | 6% | 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)) | 6% | 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.
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Look up a value between two numbers
E2, return the commission rate for the sales in E1, using the bands in A2:B6.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.
Checked by running on 2026-10-29: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.