Excel adds new argument to XLOOKUP

Barely two months after releasing XLOOKUP to select Office Insiders, Microsoft already has made changes to the new function. This makes XLOOKUP the second function ever to have a syntax change after release — joining RANDARRAY, which was updated in February (also while still available only to select Insiders).

Revising XLOOKUP

Like its sister function XMATCH, XLOOKUP is still available only in the “Office Insider” programme, which formerly was known as Insiders Fast. You can register in File -> Account -> Office Insider in Excel’s backstage area, as shown in the screenshot below. Some of you will be on commercial (“enterprise” editions) where you won’t have this option: In this instance, may I suggest you talk nicely with your friendly, local IT administrator?

After receiving some feedback from users, Microsoft decided to tweak XLOOKUP before making it and XMATCH generally available. The current line of thinking is that there should be an error trap for when a value cannot be found. This resulted in a sixth argument, [if_not_found], being added and then promoted to the fourth position, as shown below:

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

The promotion of the new argument to the fourth position means that any XLOOKUP formulas written with the original syntax and arguments in the fourth or fifth positions will no longer function correctly. This affects only Insiders who had used XLOOKUP in spreadsheets prior to the changes being made.

Despite the changes, the first three arguments remain the only ones required for XLOOKUP. Let’s look at the syntax as it stands now.

• 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.
• if_not_found: This new optional argument allows you to replace the usual return of #N/A with something more informative like an alternative formula, text, or a value.
• match_mode: This argument is optional and offers 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 the smallest value greater than or equal to lookup_value; and
  • 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 and offers 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).

It makes sense that Microsoft added if_not_found to XLOOKUP. Having this error case as an argument is consistent with other existing functions such as IFERROR and IFNA, as well as the dynamic array function FILTER. It seems like a good idea, as having this argument can tell you when something in your spreadsheet is not working as you might think. It should be noted that this argument won’t be coming to XMATCH. Whilst these functions came out at the same time and work well together, there are some subtle differences:

• XLOOKUP could retrieve #N/A from the return range even though the search item was found, so #N/A did not necessarily mean “not found”. There is no such ambiguity with XMATCH. Having this distinction for XLOOKUP is therefore useful.
• It’s likely XMATCH will be commonly used with INDEX, instead of MATCH. An [if_not_found] argument does not help here, as users will require the error trap test outside of the INDEX calculation.

Example

Let’s revisit one of my previous examples, now with the new syntax added. 

Notice in the formula bar in the screenshot above that I am searching the “Value” column, which is neither sorted nor contains unique items. Do you see how the results have changed once more, depending upon match_mode and search_mode? Basically, the new argument substitutes in “Not Found” for #NA, which is what appeared previously (as shown in the screenshot below).

Original syntax results

New syntax results

Let’s go through the new syntax results.

The match_mode zero (0) returns “Not Found” now instead of #N/A because there is no exact match and the formula has now stipulated what to do in such an instance.

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 more clear-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, although it is worth noting “Not Found” occurs when Excel identifies the lookup value has not been found.

The match_mode 2 results are spurious. This is seeking wildcard matches, but there are no matches, hence “Not Found” instead of N/A for the only search_modes that may be seen as creditable (1 and -1). It’s interesting to note a binary search causes errors that are not trapped by the new argument.

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 the binary search options”. If this is indeed the case, then I would strongly recommend not using them going forward with XLOOKUP.

Word to the wise

Remember that XLOOKUP and XMATCH are still in Preview and may only be accessed by a lucky few on the Insider track. Feel free to download and play with this Excel file, but don’t be too perturbed if your version of Excel does not recognise these functions yet.

— Liam Bastick, FCMA, CGMA, FCA, is director of SumProduct, a global consultancy specialising in Excel training. He is also an Excel MVP (as appointed by Microsoft) and author of Introduction to Financial Modelling. Send ideas for future Excel-related articles to him at liam.bastick@sumproduct.com. To comment on this article or to suggest an idea for another article, contact Jeff Drew, an FM magazine senior editor, at Jeff.Drew@aicpa-cima.com.