How to do an approximate match on data sorted in descending order
Formula (Excel and Google Sheets)
=XLOOKUP(E1,A2:A6,B2:B6,,-1)Use XLOOKUP with match mode -1, which doesn't care about sort order: =XLOOKUP(E1,A2:A6,B2:B6,,-1). VLOOKUP's TRUE needs ascending data.
- Result on the example
- B 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 the lowest score for a grade. Match mode -1 asks XLOOKUP for an exact match or the next smaller value, so 84 lands on the 80 row and returns B. XLOOKUP checks every row for this, so the table can be in any order.
=XLOOKUP(E1,A2:A6,B2:B6,,-1)Result"B"
E184The value in E1.
A2:A65 rows × 1 col: 90, 80, 70, 60, …A range: 5 cells in one column.
B2:B65 rows × 1 col: "A", "B", "C", "D", …A range: 5 cells in one column.
-1-1Makes the value negative.
XLOOKUP(E1,A2:A6,B2:B6,,-1)"B"XLOOKUP: Searches one range and returns the matching item from another.- lookup_value
E184 - lookup_array
A2:A65 rows × 1 col: 90, 80, 70, 60, … - return_array
B2:B65 rows × 1 col: "A", "B", "C", "D", … - 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 B.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =INDEX(B2:B6,MATCH(TRUE,INDEX(A2:A6<=E1,0),0)) | B | Every Excel version: in a descending table, the first row whose minimum is at or below the score is the right band. |
| =VLOOKUP(E1,SORT(A2:B6),2,TRUE) | B | Sorts the table ascending on the fly so VLOOKUP's approximate match works. Excel 2021 and Microsoft 365. |
Common mistakes
- VLOOKUP and MATCH with TRUE or 1 assume ascending order. On a descending table they return wrong grades or #N/A with no warning.
- MATCH with -1 is for descending tables of upper limits: it finds the smallest value that is at least the lookup value, which is the wrong band for a table of minimums.
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.
Do an approximate match on data sorted in descending order
E2, return the grade for the score in E1.Questions
Why not just sort the table?
Sorting ascending makes VLOOKUP with TRUE work, but grade tables are usually read top-down from A. XLOOKUP lets you keep that layout.
What does match mode 1 do?
Exact match or the next larger value, for tables that list the upper limit of each band.
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.