Created attachment 89169 [details] A screenshot showing correct and wrong returns for this function. Problem description: When LOOKUP uses a non-numeric for the search term, an error is returned. Steps to reproduce: 1. Open an instance of Calc, launch the Function Wizard. 2. Enter some data in a range, for example A1:D4, making sure the first row is sorted in ascending order. Have the first row contain numerals and letters. 3. Select a blank cell. Double-click on LOOKUP in the function list. 4. Select a letter from row 1 as the search criterion and row 1 as the search vector. 5. Select one of the remaining rows as the results vector and press OK. Current behavior: An error is returned. Expected behavior: Correct value should be returned. This same problem seems to exist for the two parameter version of this function. Operating System: Windows XP Version: 4.1.3.2 release
Strings must be entered between double quotes (for all formulae, not only LOOKUP): "E"
Hi Gerard, In LibreOffice 3.3.0 it works with 'E' (as is created automatically (and with "E" too) In 3.6.6. and further not. Didn't test with versions between 3.3.0 and 3.6.6 .. IMO a bug. thanks for the issue John. (Did not look for similar issues - sorry) Cor
was OK in 3.4.x
String was always "E" in double quotes and never in single quotes. With 'E' in single quotes it is a column/row label, looked up in the defined labels (Insert->Names->Labels...) or as sheet cell content, which depends on the position of the formula cell and if a range or an automatic intersection is to be obtained in context. As seen on the screenshot that can't work in this case because the formula is entered in the same column so no intersection can be obtained to resolve to a single cell search criterion. If this appeared to work in other cases it probably was due to luck that the formula cell and the column label formed the proper intersection.
Created attachment 89872 [details] Screenshots illustrating reply to comment 4
Created attachment 89873 [details] Screenshots illustrating reply to comment 4
(In reply to comment #4) > String was always "E" in double quotes and never in single quotes. With 'E' > in single quotes it is a column/row label, looked up in the defined labels > (Insert->Names->Labels...) or as sheet cell content, which depends on the > position of the formula cell and if a range or an automatic intersection is > to be obtained in context. As seen on the screenshot that can't work in this > case because the formula is entered in the same column so no intersection > can be obtained to resolve to a single cell search criterion. If this > appeared to work in other cases it probably was due to luck that the formula > cell and the column label formed the proper intersection. Sorry but I don't follow the explanation above. If E is entered as the search criterion, in double quotes as "E", the result returned is an error. It should be found in the search vector A2:D2 in column D. The function should then return the contents from column D from the result vector A6:D6 shouldn't it? The position of the formula makes no difference. It can be moved around the sheet with the same error result. You can replace the "E" with the word "cow" and you still get an error. If you replace the contents of the search vector's cells with text entries, then the correct result is returned. See the two new attachments which are screenshots illustrating these points. I believe this is a bug, and request that it be re-opened.
A demonstration case why screenshots aren't that helpful and instead a small testcase document is preferred. Apparently everyone who looked at the screenshot identified single quotes instead of double quotes and took that for the cause.
Created attachment 89875 [details] testcase document
(In reply to comment #8) > A demonstration case why screenshots aren't that helpful and instead a small > testcase document is preferred. Apparently everyone who looked at the > screenshot identified single quotes instead of double quotes and took that > for the cause. A good point. I'll take that on board and do that in future. Thanks.
Eike Rathke committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=f0701470858f57a855ba57c0c2283e52953db327 resolved fdo#71589 reimplemented horizontal range lookup The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Created attachment 89925 [details] a more complete testcase document
Eike Rathke committed a patch related to this issue. It has been pushed to "libreoffice-4-2": http://cgit.freedesktop.org/libreoffice/core/commit/?id=799ac18af53342597a5d3f14702de2204536c14b&h=libreoffice-4-2 resolved fdo#71589 reimplemented horizontal range lookup It will be available in LibreOffice 4.2. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Pending review for 4-1 at https://gerrit.libreoffice.org/6839
Eike Rathke committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=7d1c2f227d9e0eeb518b52c9a8251c043927d76e added unit test for horizontal MATCH, fdo#71589 The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Eike Rathke committed a patch related to this issue. It has been pushed to "libreoffice-4-2": http://cgit.freedesktop.org/libreoffice/core/commit/?id=f0ef778163161b558b969504412e6755df9acd5d&h=libreoffice-4-2 added unit test for horizontal MATCH, fdo#71589 It will be available in LibreOffice 4.2. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Pending review for 4-1-4 at https://gerrit.libreoffice.org/6912
Eike Rathke committed a patch related to this issue. It has been pushed to "libreoffice-4-1": http://cgit.freedesktop.org/libreoffice/core/commit/?id=2259f4a6a93a0dd14e961aa6eb33f49752fc86dc&h=libreoffice-4-1 resolved fdo#71589 reimplemented horizontal range lookup It will be available in LibreOffice 4.1.5. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Eike Rathke committed a patch related to this issue. It has been pushed to "libreoffice-4-1-4": http://cgit.freedesktop.org/libreoffice/core/commit/?id=3bc0b1e3bff2a1eb67b6d7f89b5087643c0db2b6&h=libreoffice-4-1-4 resolved fdo#71589 reimplemented horizontal range lookup It will be available already in LibreOffice 4.1.4. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.