How to find the next highest value in a list in Excel
Formula (Excel and Google Sheets)
=XLOOKUP(E1,B2:B7,A2:A7,"Too big",1)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.
- Result on the example
- Large 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
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.
=XLOOKUP(E1,B2:B7,A2:A7,"Too big",1)Result"Large"
E118The value in E1.
B2:B76 rows × 1 col: 15, 8, 35, 22, …A range: 6 cells in one column.
A2:A76 rows × 1 col: "Medium", "Small", "XL", "Large", …A range: 6 cells in one column.
XLOOKUP(E1,B2:B7,A2:A7,"Too big",1)"Large"XLOOKUP: Searches one range and returns the matching item from another.- lookup_value
E118 - lookup_array
B2:B76 rows × 1 col: 15, 8, 35, 22, … - return_array
A2:A76 rows × 1 col: "Medium", "Small", "XL", "Large", … - if_not_found
"Too big" - match_mode
1
- lookup_value
In Google Sheets
The same formula works in Google Sheets: =XLOOKUP(E1,B2:B7,A2:A7,"Too big",1). We ran it in Google Sheets mode on the same data and it returns Large.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =INDEX(A2:A7,MATCH(MINIFS(B2:B7,B2:B7,">="&E1),B2:B7,0)) | Large | For Excel 2019: MINIFS finds the smallest volume of at least 18, and INDEX/MATCH returns its box. |
| =MINIFS(B2:B7,B2:B7,">="&E1) | 22Different result | 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.
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.
Find the next highest value in a list
E2, return the smallest box whose volume is at least the volume needed in E1.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.
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.