Bug Hunting Session
Bug 95395 - EDITING: VLOOKUP fails if 1st parameter is a position dependent implicit intersection
Summary: EDITING: VLOOKUP fails if 1st parameter is a position dependent implicit inte...
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
4.4.6.2 rc
Hardware: All All
: medium critical
Assignee: Eike Rathke
URL:
Whiteboard: target:5.1.0 target:5.0.4 target:4.4.7
Keywords: regression
Depends on:
Blocks:
 
Reported: 2015-10-28 15:30 UTC by GerardF
Modified: 2016-10-25 19:21 UTC (History)
3 users (show)

See Also:
Crash report or crash signature:


Attachments
test file with VLOOKUP erroneus result (10.09 KB, application/vnd.oasis.opendocument.spreadsheet)
2015-10-28 15:30 UTC, GerardF
Details

Note You need to log in before you can comment on or make changes to this bug.
Description GerardF 2015-10-28 15:30:14 UTC
Created attachment 120046 [details]
test file with VLOOKUP erroneus result

When using a named range as search criterion with VLOOKUP, the formula is expected to use the criterion of the named range which is on the same relative position (same row) as the formula.

Example : VLOOKUP(column1,table,2,0) entered in D2 search for item in "column1" row 2.

With 5.0.2, VLOOKUP search always 1st item of "column1" whatever the position of the formula.

Open the attached file with 5.x and with 4.x and you will see what I mean.
Comment 1 Eike Rathke 2015-10-28 18:24:32 UTC
Investigating.
Comment 2 Eike Rathke 2015-10-28 18:33:33 UTC
Happens not only with defined names but also direct ranges => position dependent implicit intersection doesn't work.
Comment 3 Eike Rathke 2015-10-28 20:24:56 UTC
Affected are range reference arguments for scalar value parameters of functions that have a (ReferenceOr)ForceArray type parameter in their parameter list if the affected functions were NOT entered in array/matrix context:
VLOOKUP, HLOOKUP, MATCH (regression for those three) and LOOKUP (this one already earlier) in the first parameter. Also since long:
FORECAST in its first parameter, PROB, LINEST, LOGEST and TTEST in the last two parameters.
Comment 4 Commit Notification 2015-10-28 21:33:46 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=b5cd11b4b02a85a83db77ba9d8d1763f0cd88cb1

Resolves: tdf#95395 force range reference to array only in array formula

It will be available in 5.1.0.

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 5 Eike Rathke 2015-10-28 21:54:37 UTC
Pending review
https://gerrit.libreoffice.org/19659 for 5-0
https://gerrit.libreoffice.org/19660 for 4-4
Comment 6 Commit Notification 2015-10-28 22:39:19 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=9d28748d4b6d97bf0d18956e9e0336d71d9fc718

unit test for tdf#95395

It will be available in 5.1.0.

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 7 Oliver Brinzing 2015-10-31 15:16:50 UTC
.
Comment 8 Commit Notification 2015-11-02 11:44:51 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-5-0":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=956782b87d1c4a59159f9ec485f80909c19b397e&h=libreoffice-5-0

Resolves: tdf#95395 force range reference to array only in array formula, also tdf#95419

It will be available in 5.0.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.
Comment 9 Commit Notification 2015-11-03 20:30:33 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-4-4":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=61a7e6f039294103a1721ec95724d067cf205d0b&h=libreoffice-4-4

Resolves: tdf#95395 force range reference to array only in array formula, also tdf#95419

It will be available in 4.4.7.

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.