Created attachment 201381 [details] LOOKUP test cases Open attached document. The wrong cases are marked red. The situation in this cases is, that the result vector has different direction than the search vector and the result vector is given as constant inline array. If this constant array is not given directly in the formula but via a named expression, than the result is OK. I see the error in version 7.6.4. It might first occur earlier. The versions 3 to 6 crash on this file. AOO 4.1 calculates the sheet as expected.
Created attachment 201384 [details] Modified sample file Sorry @Regina but seems Zeile7={11;12;13;14;15;16;17} is a vertical vector, changing it to horizontal produce for me the same results as without named ranges. Maybe I'm wrong, but results in red are an 'Index is outside result vector' searching a vertical range in an inner horizontal range.
(In reply to m_a_riosv from comment #1) > Created attachment 201384 [details] > Modified sample file > > Sorry @Regina but seems Zeile7={11;12;13;14;15;16;17} is a vertical vector, > changing it to horizontal produce for me the same results as without named > ranges. > > Maybe I'm wrong, but results in red are an 'Index is outside result vector' > searching a vertical range in an inner horizontal range. The LOOKUP function has to work independent of the direction of the result vector. That is, when the search vector is vertical, LOOKUP has to calculate the same result for a vertical result vector as for a horizontal result vector. That works for the case the constant array for the result vector is not given directly but via a named range. It also works if you do not use a constant array but a reference to a cell range. Only in the case, that the constant array is directly given in the formula, it does not work.
I can't find where explicitly say so, in the help https://help.libreoffice.org/latest/en-US/text/scalc/01/04060109.html?DbPAR=CALC#bm_id3159273 or in ODF definition https://docs.oasis-open.org/office/OpenDocument/v1.4/OpenDocument-v1.4-part4-formula.pdf#%5B%7B%22num%22%3A361%2C%22gen%22%3A0%7D%2C%7B%22name%22%3A%22XYZ%22%7D%2C133.05%2C261.741%2C0%5D
Wiki Page https://wiki.documentfoundation.org/Documentation/Calc_Functions/LOOKUP The content of the wiki is complex, but it states that inline arrays receive special treatment. If Wiki is correct, the current behavior of the LOOKUP function appears to be correct. The result and formula are as follows: Helium =LOOKUP(2; {1;2;3}; {"Hydrogen";"Helium";"Lithium"}) Helium =LOOKUP(2; {1,2,3}; {"Hydrogen","Helium","Lithium"}) #N/A =LOOKUP(2; {1,2,3}; {"Hydrogen";"Helium";"Lithium"}) #N/A =LOOKUP(2; {1;2;3}; {"Hydrogen","Helium","Lithium"}) But in Google spreadsheet, all of the above formulas will be "Helium". And I can't find any mention of inline arrays getting special treatment in the help. I don't know about MS Excel.
Created attachment 201400 [details] Screenshot with Excel Screenshot with results in Microsoft® Excel® para Microsoft 365 MSO (versión 2505 compilación 16.0.18827.20102) de 64 bits I think it behaves as you expect with ranges, but not with named ranges.
Gnumeric as well, calculates the values as expected by me. (In reply to m_a_riosv from comment #3) > I can't find where explicitly say so, [...] in ODF definition > https://docs.oasis-open.org/office/OpenDocument/v1.4/OpenDocument-v1.4-part4- > formula. > pdf#%5B%7B%22num%22%3A361%2C%22gen%22%3A0%7D%2C%7B%22name%22%3A%22XYZ%22%7D%2 > C133.05%2C261.741%2C0%5D That is the problem; I currently work on that. The ODF description needs improvement. https://issues.oasis-open.org/projects/OFFICE/issues/OFFICE-4164 (In reply to nobu from comment #4) [..] > But in Google spreadsheet, all of the above formulas will be "Helium". > And I can't find any mention of inline arrays getting special treatment in > the help. That means, that Google spreadsheet accepts different directions of search and result vector. That supports my opinion, that LibreOffice has a bug.
Created attachment 201403 [details] Lookup test cases LO produces #N/A error too if it uses a named range instead of a direct inline array. Sorry for wrong orientated named range in my previous upload. The error happens in https://opengrok.libreoffice.org/xref/core/sc/source/core/tool/interpr1.cxx?r=b16dd0fb306944a02e5d73d4a2bbc68cf80b0b8f#7408 There incorrectly the orientation of the search vector is used.
Created attachment 201404 [details] Patch to use the result vector dimension If you agree, that it is a bug, I think it can be fixed with this patch.
@Eike: Could you please have a look? The change that produces, that a different direction is no longer possible was introduced with https://cgit.freedesktop.org/libreoffice/core/commit/?id=5079e1fb668b31989672efc097c82ced6cbf4137 That is some time ago. Should we change it back nevertheless? I would say yes, because Excel, gnumeric and AOO interpret it that way and because different directions are interpreted without error for cell references. So why not for inline arrays? Is the proposed patch the correct way for fixing it? Should ODF TC add something to explicitly allow different directions?
Created attachment 201416 [details] using of an inline array in xlsx-format, produced by Excel The attachment is for to test applications that cannot use ods format but only xlsx.
(In reply to Regina Henschel from comment #9) > That is some time ago. Should we change it back nevertheless? I would say > yes, because Excel, gnumeric and AOO interpret it that way and because > different directions are interpreted without error for cell references. So > why not for inline arrays? Yes, it was probably an oversight there that the result array may have a different direction than the query array. > Is the proposed patch the correct way for fixing it? Looks good. > Should ODF TC add something to explicitly allow different directions? Maybe best. It is somewhat implied by "If it is a cell range, it gets automatically extended to the length of the searched vector, but in the direction of the result vector." (under "The lengths of the search vector and the result vector do not need to be identical.") though being extended is not applicable to arrays, but the directions aren't explicitly mentioned otherwise.
Regina Henschel committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/5c0961129820fbc9dc8ec12c8a43a352ef1856a3 tdf#167134 Allow different directions in LOOKUP It will be available in 26.2.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-25-8": https://git.libreoffice.org/core/commit/f422d1366269a0fa611f7018dd73d933e7d2ca6e tdf#167134 Allow different directions in LOOKUP It will be available in 25.8.0.0.beta2. 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-2": https://git.libreoffice.org/core/commit/0aa79813e0e2d5b2984aa97b657f675ffa2824af tdf#167134 Allow different directions in LOOKUP It will be available in 25.2.6. 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.
Xisco Fauli committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/aea7c4ecb85ed9d92f5fe1bbd9142d9b3a6d1c76 tdf#167134: implement test as sc_functions_test It will be available in 26.2.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.
Verified Version: 26.2.0.0.alpha0+ (X86_64) / LibreOffice Community Build ID: 36a55369a6ac72ae985cf31405b7f52686665d84 CPU threads: 16; OS: Windows 11 X86_64 (build 26100); UI render: Skia/Raster; VCL: win Locale: en-US (es_ES); UI: en-US Calc: CL threaded
Regina Henschel committed a patch related to this issue. It has been pushed to "libreoffice-25-2-5": https://git.libreoffice.org/core/commit/cf9f04b05f354c5736d4bd462b1fa4c99d2f1947 tdf#167134 Allow different directions in LOOKUP It will be available in 25.2.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.