Explanation

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.

Dynamic Array Formulas are available in Office 365 only.

Explanation

One of the nice benefits of XLOOKUP is it can easily return entire rows or columns as a lookup result. This can be done also with INDEX and MATCH , but the syntax is more complex.

In the example shown, we want to retrieve all values associated with Q3. The formula in H5 is:

=XLOOKUP(H4,C4:F4,C5:F8)
  • The lookup_value comes from cell H4, which contains “Q3”
  • The lookup_array is the range C4:F4, which quarters in a header
  • The return_array is C5:F8, which contains all data
  • 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)

XLOOKUP finds “Q3” as the second item in C4:F4 and returns the second column of the return_array , the range E5:E8.

Lookup row

In the example shown, XLOOKUP is also used to lookup a row. The formula in C10 is:

=XLOOKUP(B10,B5:B8,C5:F8)
  • The lookup_value comes from cell B10, which contains “Central”
  • The lookup_array is the range B5:B8, which lists regions
  • The return_array is C5:F8, which contains all data
  • 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)

XLOOKUP finds “Central” as the third item in B5:B8 and returns the third row of the return_array , the range C7:F7.

Further processing with other functions

The results delivered by XLOOKUP can be handed off to other functions for further processing. For example, to add up all Q3 numbers, you can use the SUM function like this:

=SUM(XLOOKUP("Q3",C4:F4,C5:F8)) // sum Q3

which returns a single result, 503,250.

Dynamic Array Formulas are available in Office 365 only.