# XLOOKUP horizontal lookup

To perform a horizontal lookup with the XLOOKUP function, supply a lookup value, a lookup array, and a result array. Provide a *match_mode* argument for approximate match if needed. In the example shown, the formula in I5, copied down the table, is:

=XLOOKUP(E5,quantity,discount,,-1)

where **quantity** (C4:F4) and **discount** (C5:F5) are named ranges.

*beta function*, available only through the Office Insiders program.

### How this formula works

One nice benefit of the XLOOKUP function is that the syntax used for horizontal lookups is the same as for vertical lookups.

In the example shown, the data in C4:F5 contains quantity-based discounts. As the quantity increases, the discount also increases. The table to the right shows the discount returned by XLOOKUP for several random quantities. The formula in F5, copied down, is:

=XLOOKUP(E5,quantity,discount,,-1)

The same formula without named ranges is:

=XLOOKUP(H5,$C$4:$F$4,$C$5:$F$5,,-1)

XLOOKUP's arguments are configiured like this:

- The
*lookup_value*comes from cell E5 - The
*lookup_array*is the named range**quantity**(C4:F4) - The
*return_array*is the named range**discount**(C5:F5) - 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)

At each row, XLOOKUP looks up the quantity in the range C4:F4. When an exact match is found, the corresponding discount in row 5 is returned. When an exact match is *not found*, the discount associated with the *next smallest* quantity is returned.

### XLOOKUP vs HLOOKUP

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

There are several differences worth noting:

- HLOOKUP requires the full table array as the second argument. XLOOKUP requires only the range with lookup values.
- HLOOKUP requires a row index to specify a result column. XLOOKUP requires a range that contains result values.
- HLOOKUP performs an approximate match
*by default*. XLOOKUP performs an exact match by default. - HLOOKUP requires lookup data to be sorted by lookup value. XLOOKUP works with unsorted data.
- HLOOKUP performs horizontal lookups only, XLOOKUP can perform both horizontal and vertical lookups.

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