Created attachment 205001 [details] File with horizontal lookup for empty cell Open attached file and examine tab "test". Recalculate hard. Notice, that the results in A6, A7 and A12, A13 are wrong. A7 should be "$mini.$G$2" A13 should be "$mini.$G$2" Open the attached file in Excel. It shows the correct cell addresses in horizontal cases. I see the problem in Version: 26.2.0.0.beta1 (X86_64) Build ID: 620(Build:0) CPU threads: 32; OS: Windows 11 X86_64 (build 26100); UI render: Skia/Vulkan; VCL: win Locale: en-US (de_DE); UI: en-US Calc: threaded
Reproducible Version: 26.2.0.0.beta1 (X86_64) Build ID: 620(Build:0) CPU threads: 4; OS: Windows 10 X86_64 (build 19045); UI render: Skia/Raster; VCL: win Locale: es-ES (es_ES); UI: en-US Calc: CL threaded
Seems it never works Version: 24.8.7.2 (X86_64) / LibreOffice Community Build ID: e07d0a63a46349d29051da79b1fde8160bab2a89 CPU threads: 4; OS: Windows 10 X86_64 (10.0 build 19045); UI render: Skia/Raster; VCL: win Locale: es-ES (es_ES); UI: es-ES Calc: threaded
I have begun investigating the cause. However, I do not have a solution. To ensure that the results are not lost, here is a report: Let's look at an example with values A1=1, B1=2, C1=3 A2=CELL(“ADDRESS”;XLOOKUP(;A1:F1;A1:F1)) and an otherwise empty sheet. Cell A2 should display the string “$D$1.” Excel does this, by the way. The problem becomes clear in https://opengrok.libreoffice.org/xref/core/sc/source/core/data/queryiter.cxx in lines #131 if (nCol > maParam.nCol2 || nCol >= rDoc.maTabs[nTab]->GetAllocatedColumnsCount()) #132 return; GetAllocatedColumnsCount() has the value 3 here. As a result, cell D1 is not considered at all, even though it belongs to the search vector. You cannot simply replace >= with >. In line 145 pCol = &(rDoc.maTabs[nTab])->aCol[nCol]; the vector aCol (a ScColContainer) actually only has 3 elements, and a aCol[3] would then cause an “Expression: vector subscript out of range” assertion failure. This vector aCol has already been determined somewhere before the function call. I see the value in ScInterpreter::ScXLookup() in this.mrDoc.maTabs[0]->aCol. I don't know where aCol is calculated. It contains the columns that are actually used. This is certainly necessary in other places for performance reasons. If cell D1 contains a comment or an image, it is still empty, but the vector aCol then has 4 elements and the empty cell in the search vector is found. It is also found if the sheet contains a value somewhere in a higher column.
COUNTIF and COUNTIFS have the same problem, that only the range till the last "used" column is considered but not the entire specified cell range. Thus counting empty cells gives wrong results.
Wrong counting with COUNTIFS is tracked in bug 159544. Wrong counting with COUNTIF is in bug 170388. But it turned out that the solution for XLOOKUP will fix COUNTIF as well.
*** Bug 170388 has been marked as a duplicate of this bug. ***
Regina Henschel committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/4be8d9afe7ace4ef1289218edb92b9d422507c0f tdf#170295 no reduced range if look for empty cells It will be available in 26.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.
Regina Henschel committed a patch related to this issue. It has been pushed to "libreoffice-26-2": https://git.libreoffice.org/core/commit/99c906812a5a27b440f4f4e90a2318303bf0bf9c tdf#170295 no reduced range if look for empty cells It will be available in 26.2.1. 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.
Regina Henschel committed a patch related to this issue. It has been pushed to "libreoffice-25-8": https://git.libreoffice.org/core/commit/5934e0cd11af994a095658ebbdf82cf21e419a79 tdf#170295 no reduced range if look for empty cells It will be available in 25.8.5. 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.