# XLOOKUP basic approximate match

=XLOOKUP(value,rng1,rng2,,-1)

To use XLOOKUP to find an approximate match, supply the match_mode explicitly in the forth argument. In the example shown, the formula in F5, copied down the table, is:

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

where **qty** (B5:B13) and **disc** (C5:C13) are named ranges.

### How this formula works

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:

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.

## Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.