Created attachment 201613 [details] File to reproduce the bug easily The file contains 4 tables for each of the match-mode and search-mode combinations of 1/-1. Initially all 4 variations produce different unexpected results, but by filling in the missing D1, all but "MM 1, SM -1" produce the logical results, "MM 1, SM -1" producing similar results, but replacing the missing value with 0. Replacing the missing value with 0 is also the behavior of Excel (which is inconsistent with how it handles values lower than available to a MM -1 lookup...). Other odd behavior: For the "SM 1" variants, if you remove the D1 space by dragging over E1:F2 instead, XLOOKUP will still fail as if there was a space in the range. Clearly Libreoffice doesn't shrink the range it is looking at, thinking there are still missing values within it. One can also move E1:F2 over to the right, noticing how Libreoffice will expand the range it'll look at, but never shrink it. A "Recalculate" or "Hard recalculate" will not fix this, but reloading the file, even after saving in the broken state, will. Further Excel behavior and internal inconsistencies (KOR = Missing lookup key without next lower/higher, EK = Key without value, EV = Value without key, N = Neither key nor value, EX = Excel, LO = Libreoffice): - "MM -1, SM -1" EX(KOR: #NV, KOR+EV: #NV, EK: 0, EV: 0, N: ignored) - "MM -1, SM -1" LO(KOR: 0, KOR+EV: EV, EK: 0, EV: EV, N: breaks) - "MM -1, SM 1" EX(KOR: #NV, KOR+EV: #NV, EK: 0, EV: EV, N: ignored) - "MM -1, SM 1" LO(KOR: #NV, KOR+EV: breaks, EK: 0, EV: breaks, N: breaks) - "MM 1, SM -1" EX(KOR: 0, KOR+EV: 0, EK: 0, EV: 0, N: ignored) - "MM 1, SM -1" LO(KOR: 0, KOR+EV: EV, EK: 0, EV: EV, N: breaks) - "MM 1, SM 1" EX(KOR: 0, KOR+EV: EV, EK: 0, EV: EV, N: ignored) - "MM 1, SM 1" LO(KOR: #NV, KOR+EV: breaks, EK: 0, EV: breaks, N: breaks) IMO, in an ideal world all of these 8 lines should look exactly the same (after the EX/LO, ofc). Tested only on Win11, AMD64 and LO 24.8.6 and partially on 24.8.7 and 25.2.4. Excel was the free browser version. LG, A
Reproducible Version: 26.2.0.0.alpha0+ (X86_64) / LibreOffice Community Build ID: 4a80f1443bf3d92eb293c74af25742db56247f04 CPU threads: 16; OS: Windows 11 X86_64 (build 26100); UI render: Skia/Raster; VCL: win Locale: en-US (es_ES); UI: en-US Calc: CL threaded
@Regina, I thought you might be interested in this issue
Match Mode 1 and -1 require, that the search vector is sorted. In the examples there are empty cells in the middle and at the end of the search vector. So independent of the question where empty cells are placed in a sorted area, this search vector is surely not sorted. In case the search vector is not sorted, no error is produced but the result is simply not usable. Producing an error is not required, because that would have enormous performance consequences as evaluators would be forced to scan the search vector linearly. It is up to the author of the spreadsheet to guarantee a sorted search vector. So I think, there is no bug at all.
Even if you are right, the behavior differs from Excel. IMHO, maybe is good to have in account that the function was implemented to match with Excel.
(In reply to m_a_riosv from comment #4) > Even if you are right, the behavior differs from Excel. I was not right. I have missed, that you use linear search. For such, ordering of search vector is not needed. Excel treats blank > number. The ODF specification of XLOOKUP is not finished. So we could do it same way as Excel and ask the ODF TC to specify it that way. It is not obvious, how Google Sheet treats blank as query value.
@Balasz, I thought you might be interested in this issue