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.
Investigating.
Happens not only with defined names but also direct ranges => position dependent implicit intersection doesn't work.
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.
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.
Pending review https://gerrit.libreoffice.org/19659 for 5-0 https://gerrit.libreoffice.org/19660 for 4-4
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.
.
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.
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.