How to look up a value from a table with minimum and maximum columns
Formula (Excel and Google Sheets)
=XLOOKUP(1,(A2:A6<=F1)*(B2:B6>=F1),C2:C6,"Not eligible")Find the row whose minimum is at or below the value and whose maximum is at or above it: =XLOOKUP(1,(A2:A6<=F1)*(B2:B6>=F1),C2:C6,"Not eligible").
- Result on the example
- 7.49% in
F2 - Works in
- Excel 2021, 2024 and Microsoft 365
- Google Sheets
- Same formula
See it work
The formula is already in F2. Change any input and the result updates, or edit the formula to experiment.
How it works
The two tests check both ends of each range: A2:A6<=F1 and B2:B6>=F1. Multiplied, they are 1 only for the range 700 to 749, so XLOOKUP returns 7.49%. A score of 560 matches no range and returns the message.
=XLOOKUP(1,(A2:A6<=F1)*(B2:B6>=F1),C2:C6,"Not eligible")Result0.0749
A2:A65 rows × 1 col: 580, 620, 660, 700, …A range: 5 cells in one column.
F1702The value in F1.
A2:A6<=F1{5 rows × 1 col} TRUE, TRUE, TRUE, TRUE, …The <= operator tests at most.
B2:B65 rows × 1 col: 619, 659, 699, 749, …A range: 5 cells in one column.
B2:B6>=F1{5 rows × 1 col} FALSE, FALSE, FALSE, TRUE, …The >= operator tests at least.
A2:A6<=F1)*(B2:B6>=F1{5 rows × 1 col} 0, 0, 0, 1, …The * operator multiplies.
C2:C65 rows × 1 col: 14.99%, 11.99%, 9.49%, 7.49%, …A range: 5 cells in one column.
XLOOKUP(1,(A2:A6<=F1)*(B2:B6>=F1),C2:C6,"Not eligible")0.0749XLOOKUP: Searches one range and returns the matching item from another.- lookup_value
1 - lookup_array
A2:A6<=F1)*(B2:B6>=F1{5 rows × 1 col} 0, 0, 0, 1, … - return_array
C2:C65 rows × 1 col: 14.99%, 11.99%, 9.49%, 7.49%, … - if_not_found
"Not eligible"
- lookup_value
In Google Sheets
The same formula works in Google Sheets: =XLOOKUP(1,(A2:A6<=F1)*(B2:B6>=F1),C2:C6,"Not eligible"). We ran it in Google Sheets mode on the same data and it returns 7.49%.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =INDEX(C2:C6,MATCH(1,(A2:A6<=F1)*(B2:B6>=F1),0)) | 7.49% | For Excel 2019 and earlier (Ctrl+Shift+Enter in 2016 and older). |
| =XLOOKUP(F1,A2:A6,C2:C6,"Not eligible",-1) | 7.49% | Uses only the minimums. Fine when the ranges have no gaps, but it ignores the maximum, so a score above 850 would still get 5.99%. |
Common mistakes
- Overlapping ranges, like 620 to 660 and 660 to 699, put 660 in two rows; the first one wins. Make each maximum one less than the next minimum.
- Scores with decimals can fall between 699 and 700. Compare the next minimum instead,
B2:B6+1>F1, or make the maximums 699.99.
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 from a table with minimum and maximum columns
F2, return the APR for the credit score in F1, or "Not eligible" when no range contains it.Questions
How do I return the range as text, like "700-749"?
Join the two ends: =XLOOKUP(1,(A2:A6<=F1)*(B2:B6>=F1),A2:A6&"-"&B2:B6).
Does this work with dates?
Yes, with start and end dates in place of the minimum and maximum.
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.