# XLOOKUP and XMATCH: Two new X-Men for Excel

Microsoft has just added two new functions, XLOOKUP and XMATCH. Here I will mainly consider the former function – because once you understand XLOOKUP, XMATCH becomes obvious (nothing personal, XMATCH).

Let’s take a look at the new addition to the **LOOKUP **family. I so wanted it to be called FLOOKUP but it was not to be…

Ask anyone and they will tell you two “truths”:

- They are a better than average driver and everyone else is an idiot on the roads
- They are a better than average Excel user because they know how to use
**VLOOKUP**

It’s well known I hate **VLOOKUP **with a passion and if anything can come along and hurry its demise, well, I shall welcome it with open arms. Ladies and gentlemen, may I present the future of looking up for the masses: **XLOOKUP**. Hopefully, it will make an “ex” of **VLOOKUP**!

### Introducing XLOOKUP

There’s a new boss in town, but it’s only in selected towns presently. This function has been released in what Microsoft refers to as “Preview” mode, *i.e. *it’s not yet “Generally Available” but it is something you can try and hunt out. Presently, just like dynamic arrays, you need to be part of what is called the “Office Insider” programme which is an Office 365 fast track. You can register in **File -> Account -> Office Insider **in Excel’s backstage area.

Even then, you’re not guaranteed a ticket to the ball as only some will receive the new function as Microsoft slowly roll out these features and functions. Please don’t let that put you off. This feature will be with all Office 365 subscribers soon.

**XLOOKUP **has the following syntax:

**XLOOKUP(lookup_value, lookup_vector, results_array, [match_mode], [search_mode])**

On first glance, it looks like it has too many arguments, but often you will only use the first three:

**lookup_value:**this is required and defines what value you want to look up**lookup_vector:**this reference is required and is the row or column of data you are referencing to look up**lookup_value****results_array:**this is where the corresponding item is you wish to return and is also required (even if it is the same as**lookup_vector**). This does not have to be a vector (ie one row or one column of cells): it may be an array (with at least two rows and at least two columns of cells). The only stipulation is that the number of rows / columns must equal the number of rows / columns in the column / row vector – but more on that later**match_mode:**this argument is optional. There are four choices:**0:**exact match (default)**-1:**exact match or else the largest value less than or equal to**lookup_value****1:**exact match or else smallest value greater than or equal to**lookup_value****2:**wildcard match. You should use the special character**?**to match any character and*****to match any run of characters.

What’s impressive, though, is that for certain selections of the final argument (**search_mode**), you don’t need to put your data in alphanumerical order! As far as I am aware, this is a first for Excel.

**search_mode:**this argument is also optional. There are again four choices:**1:**search first to last (default)**-1:**search last to first**2:**what is known as a binary search, first to last (requires**lookup_vector**to be sorted). Just so you know, a binary search is a search algorithm that finds the position of a target value within a sorted array. A binary search compares the target value to the middle element of the array. If they are not equal, the half in which the target cannot lie is eliminated and the search continues on the remaining half, again taking the middle element to compare to the target value, and repeating this until the target value is found**-2:**another binary search, this time last to first (and again, this requires**lookup_vector**to be sorted).

## Industry insights

View more### XLOOKUP compares favourably with VLOOKUP

While **VLOOKUP** is the third most used function in Excel (behind **SUM **and **AVERAGE**), it has several well-known limitations which **XLOOKUP** overcomes:

**it defaults to an “approximate” match:**most often, users want an exact match, but this is not**VLOOKUP**’s default behaviour. To perform an exact match, you need to set the final argument to FALSE (as explained earlier). If you forget (which is easy to do), you’ll probably get the wrong answer**it does not support column insertions / deletions:****VLOOKUP**’s third argument is the column number you’d like returned. Since this is a hard-coded number, if you insert or delete a column you need to increment or decrement the column number inside the**VLOOKUP**– hence the need for the**COLUMNS**function (and the corresponding**ROWS**function for**HLOOKUP**)**it cannot look to the left:****VLOOKUP**always searches the first column, then returns a column to the right. There is no way to return values from a column to the left, forcing users to rearrange their data**it cannot search from the bottom:**If you want to find the last occurrence, you need to reverse the order of your data**it cannot search for next larger item:**when performing an “approximate” match, only the item less than or equal to the searched item can be returned and only if correctly sorted**references more cells than necessary: VLOOKUP**’s second argument,**table_array**, needs to stretch from the lookup column to the results column. As a result, it typically references more cells than it truly depends on. This could result in unnecessary calculations, reducing the performance of your spreadsheets.

Let’s have a look at **XLOOKUP **versus** VLOOKUP**:

You can clearly see the **XLOOKUP** function is shorter:

**=XLOOKUP(H52,F41:F47,G41:G47)**

Only the first three arguments are needed, whereas **VLOOKUP** requires both a fourth argument, and, for full flexibility, the **COLUMNS** function as well. **XLOOKUP **will automatically update if rows / columns are inserted or deleted. It’s just *simpler*.

**HLOOKUP** has similar issues:

Here, this highlights what happens if I try to deduce the student name from the Student ID. **HLOOKUP **cannot refer to earlier rows, just as **VLOOKUP **cannot consider columns to the left. Given any unused elements of the table are ignored also, it’s just good news all round. Goodbye limitations, hello **XLOOKUP**.

Indeed, things get even more interesting when you start considering **XLOOKUP**’s final two arguments, namely **match_mode **and **search_mode**, viz.

Notice that I am searching the ‘Value’ column, which is neither sorted nor contains unique items. However, I can look for approximate matches – impossible with **VLOOKUP **and / or **HLOOKUP**.

Do you see how the results vary depending upon **match_mode **and **search_mode**?

The **match_mode **zero (0) returns *#N/A *because there is no exact match.

When **match_mode** is -1, **XLOOKUP** seeks an exact match or else the largest value less than or equal to **lookup_value **(6.5). That would be 4 – but this occurs more than once (B and D both have a value of 4). **XLOOKUP **chooses depending upon whether it is searching top down (**search_mode **1, where B will be identified first) or bottom up (**search_mode **-1, where D will be identified first). Note that with binary searches (with a **search_mode** of 2 or -2), the data needs to be sorted. It isn’t – hence we have garbage answers that cannot be relied upon.

With **match_mode **1, the result is clearer cut. Only one value is the smallest value greater than or equal to 6.5. That is 7, and is related to A. Again, binary search results should be ignored.

The **match_mode **2 results are spurious. This is seeking wildcard matches, but there are no matches, hence *N/A *for the only **search_modes** that may be seen as creditable (1 and -1).

Clearly binary searches are higher maintenance. In the past, it was worth investing in them as they did return results more quickly. However, according to Microsoft, this is no longer the case: apparently, there is “…no significant benefit to using *(sic) *the binary search options…”. If this is indeed the case, then I would strongly recommend not using them going forward with **XLOOKUP**.

To show how simple it now is to search from the end, consider the following:

This used to be an awkward calculation – but not anymore! The formula is easy:

**=XLOOKUP($G$130,$G$113:$G$125,H$113:H$125,,-1)**

It’s a “standard” **XLOOKUP **formula, with a “bottom up” search coerced by using the final value of -1 (forcing the **search_mode** to go into “reverse”).

### Comparisons with LOOKUP

Whilst **XLOOKUP **wins hands down against **HLOOKUP **and **VLOOKUP**, the same cannot necessarily be said for **LOOKUP**. You may recall **LOOKUP** has two forms: an array form and a vector form. As a reminder:

- an
**array**is a collection of cells consisting of at least two rows and at least two columns - a
**vector**is a collection of cells across just one row (row vector) or down just one column (column vector).

The diagram should be self-explanatory:

The array form of **LOOKUP** looks in the first row or column of an array for the specified value and returns a value from the same position in the last row or column of the same array:

**LOOKUP(lookup_value, array)**

where:

**lookup_value**is the value that**LOOKUP**searches for in an array. The**lookup_value**argument can be a number, text, a logical value, or a name or reference that refers to a value**array**is the range of cells that contains text, numbers, or logical values that you want to compare with**lookup_value**.

The array form of **LOOKUP** is very similar to the **HLOOKUP** and **VLOOKUP** functions. The difference is that **HLOOKUP** searches for the value of **lookup_value** in the first row, **VLOOKUP** searches in the first column, and **LOOKUP** searches according to the dimensions of array.

If **array **covers an area that is wider than it is tall (*i.e. *it has more columns than rows), **LOOKUP** searches for the value of **lookup_value** in the first row and returns the result from the last row. Otherwise, **LOOKUP** searches for the value of **lookup_value** in the first column and returns the result from the last column instead.

The alternative form is the vector form:

**LOOKUP(lookup_value, lookup_vector, [result_vector])**

The **LOOKUP** function vector form syntax has the following arguments:

**lookup_value**is the value that**LOOKUP**searches for in the first vector**lookup_vector**is the range that contains only one row or one column**[result_vector]**is optional – if ignored,**lookup_vector**is used – this is the where the result will come from and must contain the same number of cells as the**lookup_vector**.

Like the default versions of **HLOOKUP **and **VLOOKUP**, **lookup_value** must be located in a range of ascending values.

Let me demonstrate with an example:

**LOOKUP **is a great function to use with time series analysis / forecasting. Dates are in ascending order and the **LOOKUP **syntax is remarkably simple. As a modeller, I use it regularly when I am modelling many more forecast periods than I want assumption periods.

Here, you can see I carry assumptions only for 2020 until 2024 (the final value is 2024, just with a “+” in number formatting). The formula

**=LOOKUP(G$74,$G$67:$K$68)**

returns the corresponding value for the period that is either an exact match or else the largest value less than or equal to the **lookup_value**. **LOOKUP** uses the top row of the table for looking up its data and the final row for returning the corresponding value. Simple. As for **XLOOKUP**:

**=XLOOKUP(G$82,$G$67:$K$67,$G$68:$K$68,-1)**

This formula is longer and requires two additional arguments (**match_mode **-1 is required to mirror the behaviour of **LOOKUP**). Indeed, given that an **IF **statement is required to ensure no errors for earlier periods, *e.g.*

**=IF(G$90<$G$67,$G$68,LOOKUP(G$90,$G$67:$K$68))**

it may be argued that **LOOKUP** is a simpler function to use here than its counterpart.

This isn’t the only time **LOOKUP **outperforms **XLOOKUP**:

Here, we do see a limitation of **XLOOKUP**. Whilst the third argument of **XLOOKUP**, **results_array**, does not need to be a vector, it cannot be the transposition of the **lookup_vector**. You would have to transpose it using the **TRANSPOSE **function, for example. This makes **LOOKUP **much easier to use – compare:

**=LOOKUP(H112,F105:F109,G102:K102)**

with

**=XLOOKUP(H112,F105:F109,TRANSPOSE(G102:K102))**

In this instance, **LOOKUP **wins.

In the next article, we will be looking at some useful features of XLOOKUP in more detail.

*Liam Bastick is author of an Introduction to Financial Modelling, which provides a simple walkthrough of the common perils and pitfalls of financial modelling. Presently, this book is only available on Amazon Australia but if anyone would like to buy a copy directly contact Liam via this link. AccountingWEB readers receive a 10% discount plus p&p (including tracking and insurance at cost).*

Recognised by Microsoft as one of 104 Most Valuable Professionals (MVPs) in Excel worldwide by Microsoft, Liam has over 30 years’ experience in financial model development/auditing, valuations, M&A, strategy, training and consultancy. He has headed Ernst & Young’s modelling team in Melbourne and was an Assistant Director in their...

## Replies (0)

## Please login or register to join the discussion.

There are currently no replies, be the first to post a reply.