# How to find the next highest value in a list in Excel

Use XLOOKUP with match mode 1, exact or next larger: `=XLOOKUP(E1,B2:B7,A2:A7,"Too big",1)`. The list doesn't need to be sorted.

**Excel:** `=XLOOKUP(E1,B2:B7,A2:A7,"Too big",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 |
| --- | --- | --- | --- | --- |
| Box | Volume (L) |  | Needed (L) | 18 |
| Medium | 15 |  | Smallest box that fits |  |
| Small | 8 |  |  |  |
| XL | 35 |  |  |  |
| Large | 22 |  |  |  |
| Tube | 12 |  |  |  |
| XXL | 50 |  |  |  |

Formula in E2: `=XLOOKUP(E1,B2:B7,A2:A7,"Too big",1)`

Result: Large

## How it works

Match mode `1` tells XLOOKUP to return an exact match or, if there isn't one, the next larger value. Nothing holds exactly 18 liters, so it picks 22, the Large box. Unlike VLOOKUP's approximate match, this works on a list in any order.

## Other ways

- `=INDEX(A2:A7,MATCH(MINIFS(B2:B7,B2:B7,">="&E1),B2:B7,0))`: For Excel 2019: MINIFS finds the smallest volume of at least 18, and INDEX/MATCH returns its box.
- `=MINIFS(B2:B7,B2:B7,">="&E1)`: When you need the volume rather than the box name. (Returns the volume, 22.)

## Common mistakes

- VLOOKUP with TRUE goes the other way, to the next smaller value, and needs the list sorted.
- If nothing is big enough, XLOOKUP returns its 4th argument, here "Too big". Without it you get #N/A.

## Questions

**How do I find the largest value below a limit?**

Use match mode -1: `=XLOOKUP(E1,B2:B7,A2:A7,"Too small",-1)`.

**How do I find the next higher value in a sorted list with MATCH?**

Sort the list descending and use `MATCH(E1,range,-1)`, which returns the smallest value that is at least E1.

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/find-next-highest-value