# Why does an approximate-match VLOOKUP return #N/A?

The value is below the first entry in the table. Give that case a result, =XLOOKUP(B2,$E$2:$E$4,$F$2:$F$4,0,-1), or start the table at 0.

**Excel:** `=XLOOKUP(B2,$E$2:$E$4,$F$2:$F$4,0,-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 | F |
| --- | --- | --- | --- | --- | --- |
| Order | Qty | Discount |  | Qty from | Discount |
| O-1 | 6 |  |  | 10 | 0.05 |
| O-2 | 25 |  |  | 50 | 0.1 |
| O-3 | 120 |  |  | 100 | 0.15 |
| O-4 | 50 |  |  |  |  |
| O-5 | 9 |  |  |  |  |
| O-6 | 75 |  |  |  |  |

Formula in C2, filled to C7: `=XLOOKUP(B2,$E$2:$E$4,$F$2:$F$4,0,-1)`

Result: 0%; 5%; 15%; 10%; 0%; 10%

## How it works

An approximate match returns the largest entry that is less than or equal to the value. A quantity of 6 is below the first tier, 10, so there is nothing to return and VLOOKUP shows #N/A. XLOOKUP's fourth argument supplies 0 for that case, and match mode -1 means "exact or next smaller".

## Other ways

- `=IFNA(VLOOKUP(B2,$E$2:$F$4,2,TRUE),0)`: For Excel 2019 and earlier: catch the #N/A for quantities below the first tier.
- `=IFNA(INDEX($F$2:$F$4,MATCH(B2,$E$2:$E$4,1)),0)`: MATCH with 1 is the same approximate match.

## Common mistakes

- The cleanest fix is a first tier that starts at 0 with a 0% discount, so every quantity has a row.
- VLOOKUP with TRUE needs the first column sorted smallest to largest; unsorted tiers give wrong rates rather than errors. XLOOKUP with -1 doesn't need sorting.

## Questions

**Why doesn't XLOOKUP need sorted tiers here?**

With the default search mode, XLOOKUP checks every row for the best match. Only the binary search modes (2 and -2) need sorted data.

**How do I look up bands defined by their upper limit?**

Use match mode 1, "exact or next larger": `=XLOOKUP(B2,upper_limits,results,,1)`.

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-lookup-returns-na