## Explanation

In the example shown, the table in B4:C13 contains quantity-based discounts. As the quantity increases, the discount also increases. The table in E4:F10 shows the discount returned by XLOOKUP for several random quantities. XLOOKUP is configured to use the quantity in column E to find the appropriate discount. The formula in F5, copied down, is:

```
=XLOOKUP(E5,qty,disc,-1)
```

- The
*lookup_value*comes from cell E5 - The
*lookup_array*is the named range**qty**(B5:B13) - The
*return_array*is the named range**disc**(C5:C13) - The
*not_found*argument is*not provided* - The
*match_mode*is set to -1 (exact match or next smaller) - The
*search_mode*is not provided and defaults to 1 (first to last)

*Note: Be aware if you supply an empty string ("") for not_found. If no match is found, XLOOKUP will display nothing instead of #N/A. In other words, an empty string doesn't mean NULL, it means "display nothing if no match is found".*

At each row, XLOOKUP looks up the quantity in column E in the range B5:B13. When an exact match is found, the corresponding discount in column C is returned. When an exact match is not found, the discount associated with the next smallest quantity is returned.

### XLOOKUP vs VLOOKUP

The equivalent VLOOKUP formula for this example is either of the two formulas below:

```
=VLOOKUP(E5,B5:C13,2) // default approximate
=VLOOKUP(E5,B5:C13,2,1) // explicit approximate
```

There are several notable differences:

- VLOOKUP requires the full table array as the second argument. XLOOKUP requires only the range with lookup values.
- VLOOKUP requires a column index to specify a result column. XLOOKUP requires a range that contains result values.
- VLOOKUP performs an approximate match
*by default*. XLOOKUP performs an exact match by default. - VLOOKUP requires lookup data to be sorted by lookup value. XLOOKUP works with unsorted data.