XLOOKUP performs lookups in vertical and horizontal cell ranges and provides support for approximate matching, partial matching (using wildcard characters like * , ? etc.) and exact matching. By default, the XLOOKUP function returns the exact matched results.

The XLOOKUP function is comparatively more flexible and powerful than the rest of the lookup functions (like VLOOKUP, HLOOKUP and LOOKUP etc.). Some more benefits of using XLOOKUP function instead of other lookup functions are listed below:

• Users can use the XLOOKUP function to lookup data to the right or left of the lookup values.
• Users can use the XLOOKUP function to retrieve data from a table.
• Users can return results from more than one column by using the XLOOKUP function.
• The XLOOKUP function can work with both vertical and horizontal data.
• The XLOOKUP function can also be used for searching data starting from the first value or the last value (reverse lookup).
• The XLOOKUP function returns a range instead of just a value and can work with generic arrays as well.

For instance - By using the XLOOKUP function, users can determine the price of a product by its product ID, find a matching tax rate in a column, search an employee name based on their employee ID and perform many other search operations while working with spreadsheets.

Syntax

XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

Arguments

This function has the following arguments:

Argument	Description
lookup_value	Refers to the lookup value.
lookup_array	Refers to the lookup array or cell range that you want to search.
return_array	Refers to the array or cell range that you want to return.
[if_not_found]	[Optional] Refers to the value to be returned when no match is found. If users don't specify this argument, and no matches are found, then the function returns #N/A error. When users specify invalid search mode (For e.g. - 0 in Excel), then #VALUE error will be returned.
[match_mode]	[Optional] Specifies the type of the match as per the following values: 0 -  Refers to exact match. If no matches are found, then #N/A error is returned. This is the default match mode. -1 - Refers to exact match. If no matches are found, then the next smaller item is returned. 1 -  Refers to exact match. If no matches are found, then the next larger item is returned. 2 -  Refers to a wildcard match where *, ? and ~ characters possess special meanings to indicate partial match.
[search_mode]	[Optional] Specifies the mode of the search as per the following values: 0 -  Refers to the "search all" mode where all the matched values will be returned. [This mode is not available in Excel.] 1 -  Refers to a search that starts at the first item. This is the default search mode. -1 - Refers to a reverse search that starts at the last item. 2  -  Refers to a binary search that depends upon the lookup_array argument being sorted in the ascending order. -2 -  Refers to a binary search that depends upon the lookup_array argument being sorted in the descending order.

Remarks

The following points must be kept in mind while working with the XLOOKUP function in the spreadsheets:

• The XLOOKUP function returns #N/A error if the lookup value is not found.
• The dimensions of the lookup_array argument must be compatible with the return_array argument for a valid result, else the XLOOKUP function will return the #VALUE! error.
• If the XLOOKUP function is used between different workbooks, then both the workbooks must be opened otherwise the XLOOKUP function will return the #REF! error.

Data Types

Accepts numeric data. Looks up values in a range or table. Returns an array with multiple items.

Examples

XLOOKUP(F2,B2:B11,D2:D11)

XLOOKUP(E2,C2:C7,B2:B7,,1,1)

XLOOKUP(B2,B5:B14,C5:D14)

Version Available

This function is available in product version 13.0 or later.

See Also

HLOOKUP | LOOKUP | Lookup Functions