# How to do an approximate match on data sorted in descending order

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.

**Excel:** `=XLOOKUP(E1,A2:A6,B2:B6,,-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 |
| --- | --- | --- | --- | --- |
| Min score | Grade |  | Score | 84 |
| 90 | A |  | Grade |  |
| 80 | B |  |  |  |
| 70 | C |  |  |  |
| 60 | D |  |  |  |
| 0 | F |  |  |  |

Formula in E2: `=XLOOKUP(E1,A2:A6,B2:B6,,-1)`

Result: B

## 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.

## Other ways

- `=INDEX(B2:B6,MATCH(TRUE,INDEX(A2:A6<=E1,0),0))`: 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)`: 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.

## 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.

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/approximate-match-descending-order