Created attachment 193748 [details] Testfile for XLOOKUP approximate search. Open attached ods document and look at sheet "next_smaller". It has data in column A and B. It examines the formula =XLOOKUP("Luchs";A2:A8;B2:B8;;-1;-2) The formula is in cell D2. LibreOffice calculates the result "Udo". Save the file in xlsx format and open the saved file in Excel 365. Excel calculates "Susanne". A similar difference can be seen with function MATCH, see sheet "exact_match". That might be related. I have not tested approximate search with ascending ordered data. But a similar error is likely. To see the problem open the unit test document sc/qa/unit/data/functions/spreadsheet/fods/xlookup.fods. Save it to xlsx and open that file in Excel. Go to Sheet2 and notice in column C the "FALSE" in row 114 and row 121. Those belong to formula =XLOOKUP($O$1;P$1:P$11;T$1:T$11;;-1;2) and =XLOOKUP($O$1;R$1:R$11;T$1:T$11;;-1;-2) respectively. [BTW: That test file is a great work!]
Repro.
Reproducible Version: 24.8.0.0.alpha0+ (X86_64) / LibreOffice Community Build ID: 0957ee9f5d379c80fca4027c187b471118d0490d CPU threads: 16; OS: Windows 10.0 Build 22631; UI render: Skia/Raster; VCL: win Locale: es-ES (es_ES); UI: en-US Calc: CL threaded
Balazs Varga committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/e2c9d38d95a1a0b5cf754fb04f2fd73c6ddb8004 tdf#160725 - Fix XLOOKUP has different result for approximate It will be available in 24.8.0. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Balazs Varga committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/85f733a9242339d8e92f992ff1ad6d1b99d3f383 Related: tdf#160725 - Add horizontal binary search mode for SortedCache It will be available in 24.8.0. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.