https://bugs.documentfoundation.org/show_bug.cgi?id=167355
Bug ID: 167355
Summary: XLOOKUP search-mode -1/1 breaks (when cell range
sparse and match-mode -1/1) (and other)
Product: LibreOffice
Version: 24.8.6.2 release
Hardware: x86-64 (AMD64)
OS: Windows (All)
Status: UNCONFIRMED
Severity: normal
Priority: medium
Component: Calc
Assignee: [email protected]
Reporter: [email protected]
Created attachment 201613
--> https://bugs.documentfoundation.org/attachment.cgi?id=201613&action=edit
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
--
You are receiving this mail because:
You are the assignee for the bug.
