# XLOOKUP lookup left

=XLOOKUP(value,rng1,rng2)

XLOOKUP can be used to find values to the left of the lookup value. In the example shown, the formula in H6 is:

=XLOOKUP(H4,E5:E14,B5:B14)

which returns 25, the height in column B for model H in row 12.

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

### How this formula works

Whereas VLOOKUP is limited to lookups to the right of the lookup column, XLOOKUP can lookup values to the left natively. This means XLOOKUP can be used instead of INDEX and MATCH to find values to the left in a table or range.

In the example shown, we are looking for the weight associated with Model H in row 12. The formula in H6 is:

=XLOOKUP(H4,E5:E14,B5:B14)

- The
*lookup_value*comes from cell H4 - The
*lookup_array*is the range E5:E14, which contains Model - The
*return_array*is B5:B14, which contains Weight - The
*match_mode*is not provided and defaults to 0 (exact match) - The
*search_mode*is not provided and defaults to 1 (first to last)

### Lookup multiple values

XLOOKUP can return more than one value at a time from the same matching record. The formula in cell G9 is:

=XLOOKUP(H4,E5:E14,B5:D14)

which returns the Height, Weight, and Price of Model H in an array that spills into the range G9:H9.

The only difference from the formula above is that return_array is entered as a range that contains more than one column, B5:D14.

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