XLOOKUP Presents Moving Arguments

It was only in late August 2019 that Microsoft added XLOOKUP to its lookup fold, and already it has had a makeover. Back in February, RANDARRAY became the first function ever to have a syntax change after release – and now XLOOKUP joins this elite group of two.

Revising XLOOKUP

Like its sister function XMATCH, XLOOKUP is still only available in the “Office Insider” programme, which is an Office 365 fast track. You can register in File -> Account -> Office Insider in Excel’s backstage area:

Please note that there is still some debate as to whether all Insiders Fast subscribers have access to these functions. 

I think Microsoft has decided to make a change before pulling the pin and making both functions Generally Available.  The current line of thinking is that there should be an error trap for when a value cannot be found.  This has meant a sixth argument has now been added:

It still means most of the time you will only require the first three arguments:

• 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 (i.e. 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.  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).

It’s an interesting amendment as the syntax order has been changed – which is a first for Microsoft.  Presumably, the thinking was this argument will be important to many users and hence has been prioritised accordingly.

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 additional 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 does 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 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?

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 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, 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 which 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 (sic) 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

XLOOKUP and XMATCH open up new avenues for Excel to explore, but it must be remembered they are still in Preview and may only be accessed by a lucky few on the Insider track.  Feel free to download and play with the attached Excel file, but don’t be too perturbed if your version of Excel does not recognise these functions yet.