Director SumProduct
Share this content
Tags:

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).

3rd Sep 2019
Director SumProduct
Share this content
istock_andreypopov_ss

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”:

  1. They are a better than average driver and everyone else is an idiot on the roads
  2. 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.

Excel account

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).

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:

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:

HLOOKUP

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.

XLOOKUP

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?

Excel match 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:

XLOOKUP

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:

Array versus vector

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:

HLOOKUP and VLOOKUP

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:

HLOOKUP and VLOOKUP

 

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).

Tags:

Replies (0)

Please login or register to join the discussion.

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