The help for MATCH at https://help.libreoffice.org/7.0/en-US/text/scalc/01/04060109.html?DbPAR=CALC#bm_id3158407 is misleading. I see that this problem was raised with Bug ID 144770, and supposedly the help was modified to correct it, but actually the help (at the URL I give above) still says what it said back when the other person complained! The help says: "If Type = 1 or the third parameter is missing, the index of the last value that is smaller or equal to the search criterion is returned. This applies even when the search array is not sorted." But it should say something like: "If Type = 1 or the third parameter is missing, the index is returned of the last value encountered smaller or equal to the search criterion before encountering a value larger than the search criterion while going through the array. If you want the index of the highest value smaller or equal to the search criterion then the search array must sorted in ascending order." That's rather complicated, but that's what Match does. For example, if the array is 0 2 1 and you search for 1, Match returns 1 (the index of the 0), not 3 (the index of the 1).
You refer to bug 144770, which was resolved for version 7.3; and point to ancient help for version 7.0. Look at help for version 7.3 ( https://help.libreoffice.org/7.3/en-US/text/scalc/01/04060109.html?DbPAR=CALC#bm_id3158407 ), or the current version at https://help.libreoffice.org/latest/en-US/text/scalc/01/04060109.html?DbPAR=CALC#bm_id3158407. The version for old versions will always stay with the text as it was released for that version.
Well, thank you for that, but the updated help is still wrong, as I explained. It wasn't enough just to remove the sentence "This applies even when the search array is not sorted." Also, I got the old help version because I clicked on "Help". I just checked, and I have Version 7.0.6.2. But why do I have an old version? I get automatic updates every few weeks!
(In reply to Eric Kvaalen from comment #2) > Well, thank you for that, but the updated help is still wrong, as I > explained. It wasn't enough just to remove the sentence "This applies even > when the search array is not sorted." Current help text is correct. It describes that the 1 and -1 assume sorted array; and it describes how it works. It may be possibly improved similar to how we improved similar argument description in VLOOKUP in commits 7179225dccf23dbe347ca6ad88e4eb22cc0abf6a and 572a8132e0e460b17c80c456dfb5e07f6b9a9c6a - Olivier, do you have an idea about good wording? > Also, I got the old help version because I clicked on "Help". I just > checked, and I have Version 7.0.6.2. But why do I have an old version? I get > automatic updates every few weeks! No idea - and you didn't even care to tell what your OS is, let alone what "automatic updates every few weeks" would mean...
No need to insult me. Why should I include what operating system I have? And "automatic updates every few weeks" means just that. Every few weeks, or maybe it's every few months, LibreOffice tells me that it has been updated. And I find that when that happens I have to redo certain settings, such as what key to press if I want autocompletion to take effect. So why am I being "updated" with apparently an old version? I don't know. I think it's a legitimate question. And what should I do about it? OK, I see now that the new help does say, higher up than the sentence about what Type=1 does, that LibreOffice assumes the array is sorted. But it doesn't say, when describing what it does, that if it's not sorted it won't work!
(In reply to Eric Kvaalen from comment #4) > Why should I include what operating system I have? Several reasons. One is that filing a bug suggests to do so. Another is that update is definitely OS-specific, so would be different on macOS, Windows, and Linux (and then, would also be different depending on installation method - e.g., using an installer from TDF, or am app store, or a distro repository...). But then, it is all off-topic here. And if you want to resolve your "old version" issue, start with a question on Ask LibreOffice, with the required details - including not only OS, but also what that "every few months, LibreOffice tells me that it has been updated" might mean (specific wording, if possible). > OK, I see now that the new help does say, higher up than the sentence about > what Type=1 does, that LibreOffice assumes the array is sorted. But it > doesn't say, when describing what it does, that if it's not sorted it won't > work! Possibly this can be improved. (But generally, it is strange to expect, that a manual telling in a respective section, that a car is expected to use gasoline, would tell on each page, that "pressing accelerator will not work, if you have water in the tank".)
Proposed text: -----8<---- If Type = 1 or the third parameter is missing, the function returns the index of the last value encountered that is smaller than or equal to the search criterion. The search stops at this match and the remaining values of the lookup array is ignored. To obtain the index of the highest value that is smaller than or equal to the search criterion, the search array must be sorted in ascending order. -----8<---- Does it works?
(In reply to Olivier Hallot from comment #6) Thanks Olivier! > If Type = 1 or the third parameter is missing, the function returns the > index of the last value encountered that is smaller than or equal to the > search criterion. > > The search stops at this match and the remaining values of the lookup array > is ignored. > > To obtain the index of the highest value that is smaller than or equal to > the search criterion, the search array must be sorted in ascending order. Technically you are indeed correct. The binary search algorithm does that: as soon as it encounters an adjacent pair of values, one of which is lower (or equal) than the key, and one higher, it stops and returns the lower one. However, in case of unsorted array, this text will likely be too precise, to the degree that it will mislead people. E.g., people might imagine, that it will find *the first one in the array*, when in reality, it will be the first one found by bisection, i.e. by always looking at the center of the examined range; and with that in mind, you can see that the returned value could happen to be ~any in the unsorted array. In principle, we must only explain what happens when it is sorted, and warn that it won't work on unsorted, not to hint on what the result could be in unsorted case. I suggest to avoid the distinction between the "highest" and "last" value that is smaller or equal. Something like "in sorted array, it finds the last value not lower than the search key. If the array is not sorted, the result is undefined" (where "undefined" means "you should not rely on what will be the result", not "the function will return some special "UNDEFINED" value" - no idea how to word that correctly). And of course, the same (modulo the sort direction) for -1.
(In reply to Mike Kaganski from comment #7) > (In reply to Olivier Hallot from comment #6) (snip) > In principle, we must only explain what happens when it is sorted, and warn > that it won't work on unsorted, not to hint on what the result could be in > unsorted case. > (snip) I agree. The ODF standard [1] does not specify what happens with Type +1 and -1 values if the lookup array is not sorted. Thus, I think this is implementation-dependent and as you point, if Calc uses a binary search, the results is indeed unpredictable. [1] https://docs.oasis-open.org/office/OpenDocument/v1.4/OpenDocument-v1.4-part4-formula.html#__RefHeading__1018430_715980110 "If MatchType is 1 or -1, evaluators may use binary search or other techniques so that they do not need to examine every value in linear order."
Olivier Hallot committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/help/commit/5beb12ef9f0c6099867dafbe1f16c7b43f6bdbb5 tdf#168064 Precision on MATCH function