Download it now!
Bug 71589 - Horizontal range lookup of string in sorted but mixed data broken; MATCH, LOOKUP, HLOOKUP
Summary: Horizontal range lookup of string in sorted but mixed data broken; MATCH, LOO...
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
3.5.0 Beta0
Hardware: Other All
: medium normal
Assignee: Eike Rathke
URL:
Whiteboard: BSA target:4.3.0 target:4.2.0.0.beta2...
Keywords: regression
Depends on:
Blocks:
 
Reported: 2013-11-13 21:37 UTC by John Smith
Modified: 2020-04-13 03:33 UTC (History)
3 users (show)

See Also:
Crash report or crash signature:


Attachments
A screenshot showing correct and wrong returns for this function. (6.55 KB, image/png)
2013-11-13 21:37 UTC, John Smith
Details
Screenshots illustrating reply to comment 4 (45.21 KB, image/png)
2013-11-26 22:23 UTC, John Smith
Details
Screenshots illustrating reply to comment 4 (13.42 KB, image/png)
2013-11-26 22:24 UTC, John Smith
Details
testcase document (9.00 KB, application/vnd.oasis.opendocument.spreadsheet)
2013-11-27 00:19 UTC, Eike Rathke
Details
a more complete testcase document (13.34 KB, application/vnd.oasis.opendocument.spreadsheet)
2013-11-27 23:19 UTC, Eike Rathke
Details

Note You need to log in before you can comment on or make changes to this bug.
Description John Smith 2013-11-13 21:37:16 UTC
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
Comment 1 GerardF 2013-11-13 21:52:45 UTC
Strings must be entered between double quotes (for all formulae, not only LOOKUP): "E"
Comment 2 Cor Nouws 2013-11-13 22:37:52 UTC
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
Comment 3 Cor Nouws 2013-11-13 23:12:12 UTC
was OK in 3.4.x
Comment 4 Eike Rathke 2013-11-26 00:31:48 UTC
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.
Comment 5 John Smith 2013-11-26 22:23:16 UTC
Created attachment 89872 [details]
Screenshots illustrating reply to comment 4
Comment 6 John Smith 2013-11-26 22:24:26 UTC
Created attachment 89873 [details]
Screenshots illustrating reply to comment 4
Comment 7 John Smith 2013-11-26 22:35:02 UTC
(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.
Comment 8 Eike Rathke 2013-11-27 00:17:51 UTC
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.
Comment 9 Eike Rathke 2013-11-27 00:19:33 UTC
Created attachment 89875 [details]
testcase document
Comment 10 John Smith 2013-11-27 14:11:21 UTC
(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.
Comment 11 Commit Notification 2013-11-27 23:17:25 UTC
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.
Comment 12 Eike Rathke 2013-11-27 23:19:17 UTC
Created attachment 89925 [details]
a more complete testcase document
Comment 13 Commit Notification 2013-11-28 00:06:05 UTC
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.
Comment 14 Eike Rathke 2013-11-28 00:17:31 UTC
Pending review for 4-1 at https://gerrit.libreoffice.org/6839
Comment 15 Commit Notification 2013-11-28 00:59:57 UTC
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.
Comment 16 Commit Notification 2013-11-28 01:20:51 UTC
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.
Comment 17 Eike Rathke 2013-12-03 14:48:01 UTC
Pending review for 4-1-4 at https://gerrit.libreoffice.org/6912
Comment 18 Commit Notification 2013-12-03 15:40:22 UTC
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.
Comment 19 Commit Notification 2013-12-10 11:29:05 UTC
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.