# Excel adds new argument to XLOOKUP

Though not yet generally available, the new function gets a rare syntax shakeup.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**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,**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**.*