Learn How to Use Microsoft Excel XLOOKUP Function

Microsoft Excel XLOOKUP Function

The primary function of the Microsoft Excel XLOOKUP function is lookup values in a range or array. The XLOOKUP function is modern and flexible than its other counterparts like - VLOOKUP, HLOOKUP, and LOOKUP. The XLOOKUP function supports approximate and exact matching, wildcards for partial matching, and lookups in horizontal and vertical ranges.     

Return Value of XLOOKUP Function

The return value will be the matching values from the return array.

Syntax of XLOOKUP Function

=XLOOKUP(lookup, lookup-array, return-array, [not-found], match-mode], [search-mode])

Where the arguments:

lookup: This is the lookup values.

lookup-array: This is the array or range to search.

return-array: This is the array or range to return.

not-found: This is the value to return if no match is found (optional).

match-mode: 0 = exact match (default), -1 = exact match or next smallest, 1 = exact match or next larger, 2 = wildcard match (optional).

search-mode: 1 = search from first (default), -1 = search from last, 2 = binary search ascending, -2 = binary search descending (optional).

How to Use Microsoft Excel XLOOKUP Function?

The XLOOKUP function is the modern replacement of the VLOOKUP function; it is more flexible and versatile and used in various situations. The XLOOKUP function can find the values in horizontal and vertical ranges, perform approximate and exact matches, and support wildcards for partial matches.

Match Type                           Behavior

0 (default)                                 Exact match, and return #N/A if no match

-1                                             Exact match or next smaller item

1                                              Exact match or next larger item

2                                              Wildcard match

Search Mode                          Behavior

1 (default)                              Search from the first value

-1                                             Search from last value

2                                              Binary search values sorted in an ascending order

-2                                             Binary search values sorted in the descending order

Check out the list of all other articles:

• Learn How to Get nth Match in Microsoft Excel
• Learn How to Find Missing Values in Microsoft Excel
• Learn How to Get nth Smallest Value With Criteria in Microsoft Excel
• Learn How to Sort by Text Length in Microsoft Excel
• Learn How to Sort by Two Columns in Microsoft Excel
• Learn How to Sort by Random in Microsoft Excel
• Learn How to Sort by One Column in Microsoft Excel
• Learn How to Sort by Custom List in Microsoft Excel