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