# How to look up a value from a table with minimum and maximum columns

Find the row whose minimum is at or below the value and whose maximum is at or above it: `=XLOOKUP(1,(A2:A6=F1),C2:C6,"Not eligible")`.

**Excel:** `=XLOOKUP(1,(A2:A6<=F1)*(B2:B6>=F1),C2:C6,"Not eligible")` (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 | F |
| --- | --- | --- | --- | --- | --- |
| Min score | Max score | APR |  | Credit score | 702 |
| 580 | 619 | 0.1499 |  | APR |  |
| 620 | 659 | 0.1199 |  |  |  |
| 660 | 699 | 0.0949 |  |  |  |
| 700 | 749 | 0.0749 |  |  |  |
| 750 | 850 | 0.0599 |  |  |  |

Formula in F2: `=XLOOKUP(1,(A2:A6<=F1)*(B2:B6>=F1),C2:C6,"Not eligible")`

Result: 7.49%

## How it works

The two tests check both ends of each range: `A2:A6=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.

## Other ways

- `=INDEX(C2:C6,MATCH(1,(A2:A6<=F1)*(B2:B6>=F1),0))`: For Excel 2019 and earlier (Ctrl+Shift+Enter in 2016 and older).
- `=XLOOKUP(F1,A2:A6,C2:C6,"Not eligible",-1)`: 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.

## Questions

**How do I return the range as text, like "700-749"?**

Join the two ends: `=XLOOKUP(1,(A2:A6=F1),A2:A6&"-"&B2:B6)`.

**Does this work with dates?**

Yes, with start and end dates in place of the minimum and maximum.

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

Source: https://spreadsheetpractice.com/formulas/lookup-min-max-columns