The help states: Syntax
XLOOKUP(Lookup; Array; Return [; NotFound; MatchType; SearchMode ] )
Unfortunately it is more complex. (1) Lookup is optional. (2) Each of NotFound, MatchType and SearchMode is individually optional, not as a block:
XLOOKUP( [Lookup] ; Array ; Return [ ; [ Notfound ] [ ; [MatchType] [ ; SearchMode ] ] ] )
Is it somewhere else mentioned in the Help, how to write a missing optional parameter in between? If not, a second example would be useful which shows this.
The help states:
Array: is the reference of the array or range to search.
Array need not be a reference, it can be an inline array as well. Array cannot be a range. Array must be an 1-dimensional array.
The help states:
note
If Return is an array of values, the XLOOKUP function must be entered as an array formula.
Entering as array formula is not needed, if Return is an “array”, but if it is a “range”.
The help states:
NotFound: a text to return if the Lookup value is not found. If a valid match is not found, and NotFound is omitted, returns #N/A.
NotFound can not only be a text but any content, especially numbers are allowed as well.
“returns #N/A” should be “returns the #N/A error” to make clear, that it is not a text but a special error value. That applies to MatchType value 0 too.
The help states, that MatchType 1 and -1 do an “exact match”. That is wrong. It is an “approximate match”.
The help should mention, that XLOOKUP ignores the setting “Case sensitive” in the Options and is case-insensitive as default. It is possible to create a case-sensitive search by using a mode modifier in an regular expression. But how to do that is more a topic for the Wiki than for the Help.
Combining MatchType 2 with SearchMode 2 or -2 is not possible. The current implementation switches to linear search in such cases. But that will likely be changed to produce an error, see discussion on the dev-list.
The example uses Comma as delimiter between the parameters. That is inconsistent. It should be a Semicolon as in the syntax and in other examples in the help.
The XLOOKUP function is currently in discussion in the ODF TC. In case you are interested in the draft of the specification, I can sent it to you. Give me an email address where to sent it in that case.
I would suggest improving the names of the arguments, especially considering screen readers. Having argument names such as “Lookup”, “Array” or “Return” is confusing and requires special attention to text formatting.
ATM, the Function Wizard is already using a different set of names for the arguments. I am not saying they are the recommended names (and perhaps alternative argument’s names should be considered), but at least they can be differentiated from the simple terms currently mentioned in the Help.
BTW, the FW arguments for Match_mode and Search_mode should have the usual text explanation in order to reduce confusion. Currently, they are “0, -1, 1 or 2” and “1, -1, 2 or -2” respectively, without specifying their meanings. Considering that similar (older) functions also use similar arguments but with different meanings and different defaults, specifying the meaning/implications of each of these values will be very important for users.
I am not sure what exactly you are looking for. IMHO, " [Lookup] ;" is clear. As for the other arguments being optional but not as a block…
I have read something about that (general statement) somewhere, but I cannot remember where exactly; it might be in some wiki page/template regarding Calc functions. Maybe someone else can find it.
FWIW, the wiki page of the INDEX() function mentions something of that sort (but perhaps easily missed by some readers(?)):
In the array context, omitting the Column argument (with two consecutive semicolons)…
Considering the relative complexity of the XLOOKUP() function and the amount of optional arguments, I think that maybe an explicit paragraph might be the simplest way to explain it. For instance:
“Any of the optional arguments can be omitted, but keep in mind that for each argument explicitly used in the formula, every-and-all prior argument’s separators must be used.”
One of the basic possible cases could follow after such phrase as an example: XLOOKUP(SearchCriterion; SearchArray; ResultArray ; ; ; SearchMode )
IMHO, phrases such as “Array cannot be a range” are confusing for most common users. Advance experienced users might get the subtleties. "(Search_Array can be a 1-dimensional range or a 1-dimensional array) should be more clear, although I am not sure it is completely accurate either – it would need to be re-checked.
Strictly speaking, it depends on what the expected result should be.
During the several bug reports about XLOOKUP(), I have used ranges as arguments without having to press CSE. OTOH, always using CSE would/should at least work. even if the result would be a 1x1 “array”.
Additionally, maybe the two possible outcomes (only display first result value vs display complete Result_Array) should be mentioned somewhere (in the wiki perhaps?) instead of claiming that a simple [ENTER] is not possible.
I guess it all depends on how the term “exact” is defined in this context, considering that the search is indeed initially performed looking for the exact match for 1 and -1, and the approximate result is only provided as a second “best-fit” or “closely-fit” approximate alternative.
0: Exact match (exclusively)
-1: Exact + Approximate match. If an exact match is not found, then return the next “smaller” item.
1: Exact + Approximate match. If an exact match is not found, then return the next “larger” item.
In any case, be aware of the possible use of “close” or similar terms, as they are often (incorrectly) interpreted as the closest to the exact matching value, frequently forgetting about the other conditions. This type of confusions must be exposed by examples in the (future) wiki page for XLOOKUP().
Since I have no access to Excel ATM, I cannot test this by myself.
The dev-list is for developers (not common users), while mentioning that they look for user’s input about the matter in question. So here are a couple of questions (and please read this as a real question and perhaps as a “thinking out loud”, not as a critique nor a complain in any way):
What would be the point of throwing some error when combining MatchType 2 with SearchMode 2 or -2, instead of silently switching to linear search?
I mean, other than some compatibility to Excel’s results (which is very important, for sure), when receiving an error in this case, what the user should do? Are there several alternatives?
When receiving an error, the user would need to manually modify at least one of the two arguments. So, when receiving an error, is the intention that the user should review both arguments and select what to change and what not?
My point is that, if there is only one sensible manual change for the user, then that would probably be the automatic mode that should be silently taken, and avoid bothering the user. But, no so fast…
The next issue I am thinking about is that, when using MatchType 2 with SearchMode 2 or -2, the initial intention of the user might be to avoid lengthy search operations. If the formula would take the slower linear search in this case, then either an error (as if it was a warning/info type) is acceptable, or instead the user would need to be able to cancel the lengthy search, which cannot be done ATM IINM.
I guess that in some sense I am justifying the reasoning to throw an error instead of silently executing a potentially lengthy operation without being able to cancel it. I also guess that this is the reasoning used in Excel too (because in older functions this kind of search-type selection was not in the hands of the user).
But I am also, in some other sense, “explaining” such error to this user that typed the formula, in order for the error to have some real meaning: what the users’ choices are? And what they could actually do (and their consequences) when receiving this error?
@All, Please don’t forget that there is still at least one open bug report about XLOOKUP(), started by Regina (just as this discussion) and with a very important factor (internal cell value vs display value), tdf#160893. This should rather be solved before LO 24.8 beta arrives.
“Array cannot be a range” was not meant as text for the user, but as hint for the help authors. The term “range” implies, that something like $Sheet1.A1:$Sheet7.A1 can be used. But that is not the case. The term “array” implies, that the cells are all on the same sheet and consecutive. Therefore the ODF specification will use the term 1-dimensional array.
The Lookup_Array (as it will be named in the ODF specification) cannot span over several sheets.
You are right. “array” is indeed correct here. Using cells from different sheets is not possible for Return_array. If Lookup_array is an 1-dimensional vertical array and Return_array is an 1-dimensional vertical array, then CSE is not necessary. If Lookup_array is an 1-dimensional vertical array and Return_array is a 2-dimensional array, then CSE is necessary and the result is an 1-dimensional horizontal array. Such use surely needs an example at least in the Wiki. It would be good in general to have examples in the Wiki before release. The XLOOKUP function is very complex.
Regarding the “array” vs “range” terminology (from the POV of common users/readers), there is one factor that might lead to misinterpretation.
While a so-called 3D-range (i.e. including worksheet names) is not accepted as argument for XLOOKUP(), a simple 1-dimensional area can be used. I am using here the term “area” on purpose.
Some users might understand the term “array” as having to use curly brackets “{}” around the references, similar to an “inline array”, or similar to a “formula array”, introduced with CSE.
But arguments for XLOOKUP() might not need the curly brackets. For example, in one of the bug reports, I used:
=XLOOKUP("nit*";A$2:A$11;D$2:D$11; ;E31)
…without even introducing the formula with CSE. Incidentally, I also used the same formula in a different cell but introduced with CSE. Both cases were accepted and valid (within the context of the bug report).
As you can see, the arguments look as simple ranges (within the same worksheet, without worksheet names) and they don’t use curly brackets.
These are valid arguments. This is my point regarding potential misinterpretations. This type of arguments – references which common users frequently would call “ranges”, nor necessarily “arrays” – are valid, and users should understand from the Help content that they are valid, instead of (wrongly) assuming that the arguments must be typed between curly brackets as “arrays”, or incorrectly assuming that XLOOKUP() cannot be introduced with a sole [ENTER] in some cases (because it can).
Unfortunately, the term “range” and “array” are frequently misused or misinterpreted by users (or partially unknown). The Help content should try to overcome such potential misinterpretation (instead of assuming that readers are aware of the subtle differences and limitations), and help users anyway.
