The XLOOKUP Function | Full Stack Modeller

Looks up values from another table

What does Excel's XLOOKUP function do?

The XLOOKUP function is one of Excel's lookup and reference functions.

In modelling we use the XLOOKUP function to map data from one table into another based on a unique reference.

XLOOKUP is a much improved version of existing lookup methods such as VLOOKUP, HLOOKUP and INDEX MATCH.

The function includes an optional argument of what to return if no match is found. This removes the need for separate error management functions.

Here's a simple example

Let's take a look at a simple example of the XLOOKUP function.

We have a table of products and their prices.

In cell B7 we would like to return the price of the product that is entered in cell A7.

We would write the formula using XLOOKUP as follows:

=XLOOKUP(A7, A2:A5, B2:B5, "Not Found")

What does that mean in plain English?

Return the price of Lamb Pies from the product table.

Let's take a closer look at the structure of the arguments in the formula:

lookup - We want to find the price for Lamb Pie. 

lookup array - We are looking for Lamb Pie in the range A2 to A5.

return array - The range B2 to B5 contains the product prices we want to choose from.

[not found] - Return the text "Not Found" if the lookup cannot be found in the lookup array

How do I write a formula using the XLOOKUP function?

= XLOOKUP (lookup, lookup_array, return_array, [not_found], [match_mode], [search_mode])

lookup - The value that you want to match.

lookup_array - The range of cells being searched through for the match value.

return_array - The range of cells that you want to return a value from.

[not_found] - Value to return if no match is found.

[match_mode] - Defines how the match should be performed:1 for next lower, -1 for next higher, 0 for exact match and 2 for a wildcard match.

[search_mode] - 1 for search from first (default), -1 for search from last,2 for binary search ascending, -2 for binary search descending.

What to consider when using the XLOOKUP function in your financial model

• The function includes an optional argument of what to return if no match is found. This removes the need for separate error management functions.
• As with other reference functions, we are able to choose an exact or approximate match using the match mode argument.
• XLOOKUP also includes the additional feature of defining whether the search is performed top to bottom or bottom to top using the search mode argument.
• The function can take advantage of the dynamic array engine, returning multiple entries for one formula.
• The XLOOKUP function only works for Office 365 users. This raises a significant compatibility risk.

Read more about the XLOOKUP function on the Microsoft support page here.