Description: If you enter the formula: =XLOOKUP("A",{"A"; "B"},{1;2},"") in Calc, you will not get the expected result "A" but the content from cell A1 of the first worksheet. XLOOKUP works if you have stored the search array and result array in cells somewhere in the worksheet and refer to it. Steps to Reproduce: 1. write "Hello world" in cell A1 2. write the formula =XLOOKUP("A",{"A"; "B"},{1;2},"") in any other cell in the worksheet Actual Results: "Hello world" Expected Results: "A" Reproducible: Always User Profile Reset: Yes Additional Info: With =XLOOKUP("B",{"A"; "B"},{1;2},"") you get the result of cell B1. To see changes you must recalculate Hard. In Microsoft Excel you get the correct result "A".
Created attachment 197498 [details] Example document
Sorry ich habe mich verschrieben: Richtig lautet die Beschreibung: Description: If you enter the formula: =XLOOKUP("A",{"A"; "B"},{1;2},"") in Calc, you will not get the expected result 1 but the content from cell A1 of the first worksheet. XLOOKUP works if you have stored the search array and result array in cells somewhere in the worksheet and refer to it. Steps to Reproduce: 1. write "Hello world" in cell A1 2. write the formula =XLOOKUP("A",{"A"; "B"},{1;2},"") in any other cell in the worksheet Actual Results: "Hello world" Expected Results: 1 Reproducible: Always User Profile Reset: Yes Additional Info: With =XLOOKUP("B",{"A"; "B"},{1;2},"") you get the result of cell B1. To see changes you must recalculate Hard. In Microsoft Excel you get the correct result 1.
Currently XLOOKUP is implemented so that 'Return array' has to be a reference, Thus {1;2} is not allowed as 'Return array' and should result in an error. The current result is surely wrong. But I can think of changing the implementation so that an inline array as 'Return array' is possible too. That needs discussion in the ODF TC.
Cannot reproduce with: =XLOOKUP("A";{"A"; "B"};{1;2},"") returns the expected result »1« @Regina I cannot confirm that either the »inline return-array is not allowed«?! with me: _________ Version: 24.8.2.1 (AARCH64) / LibreOffice Community Build ID: 0f794b6e29741098670a3b95d60478a65d05ef13 CPU threads: 4; OS: Linux 6.6; UI render: default; VCL: gtk3 Locale: de-DE (de_DE.UTF-8); UI: de-DE Flatpak Calc: threaded ps: @Jürgen please use ;semicolon; as Argumentseperator!
(In reply to Werner Tietz from comment #4) > Cannot reproduce with: > =XLOOKUP("A";{"A"; "B"};{1;2},"") > returns the expected result »1« > > @Regina I cannot confirm that either the »inline return-array is not > allowed«?! Do you have opened the attached "Example document" (attachment 197498 [details])? I have used Version: 25.2.0.0.alpha0+ (X86_64) / LibreOffice Community Build ID: 9517639bc3189e3ea4dc4d2f7004d4b33d754d47 CPU threads: 32; OS: Windows 11 X86_64 (10.0 build 22631); UI render: Skia/Raster; VCL: win Locale: de-DE (de_DE); UI: en-US Calc: threaded
(In reply to Werner Tietz from comment #4) > Cannot reproduce with: > =XLOOKUP("A";{"A"; "B"};{1;2},"") > returns the expected result »1« !!sorry my bad, it returns »1« because Cell A1 of the first sheet value was »1« !! ⇒ confirmed > > with me: > _________ > Version: 24.8.2.1 (AARCH64) / LibreOffice Community > Build ID: 0f794b6e29741098670a3b95d60478a65d05ef13 > CPU threads: 4; OS: Linux 6.6; UI render: default; VCL: gtk3 > Locale: de-DE (de_DE.UTF-8); UI: de-DE > Flatpak > Calc: threaded
Balazs Varga committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/f5702d1f27d72e5f70715b04f58b7c76bb1f7915 tdf#163821 - sc fix XLOOKUP does not work correctly if the search It will be available in 25.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 "libreoffice-24-8": https://git.libreoffice.org/core/commit/f16b47a13cc6d870b35da596f5c5b62bd94d74ac tdf#163821 - sc fix XLOOKUP does not work correctly if the search It will be available in 24.8.7. 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 "libreoffice-25-2": https://git.libreoffice.org/core/commit/3f66676322126722b820bba4a4b0942adea01087 tdf#163821 - sc fix XLOOKUP does not work correctly if the search It will be available in 25.2.4. 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 "libreoffice-25-2-3": https://git.libreoffice.org/core/commit/52dd16cbb636c4c01a24b66cf49e24e154823c6e tdf#163821 - sc fix XLOOKUP does not work correctly if the search It will be available in 25.2.3. 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.