# XLOOKUP Presents Moving Arguments

5 November 2019

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:

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

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

**lookup_value:**this is required and defines**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.

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

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

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.