Description: Using in the 2nd sheet the formula =LOOKUP(1,1/(A$2:A$11=E$1),1) we get data from the 1st sheet if the search data is in the lookup vector (please see attached example) Steps to Reproduce: Follow steps in attachment or start from empty sheet: 1. in 1st sheet in cell B1 put "unrelated data" 2. in 2nd sheet in cell E1 put "k2" 3. in 2nd sheet A2:A11 put the values "k1" to "k10" 4. in 2nd sheet in cell E4 put the formula =LOOKUP(1,1/(A$2:A$11=E$1),1) Actual Results: The formula delivers "unrelated data" Expected Results: Either showing an error or #N/A Reproducible: Always User Profile Reset: Yes Additional Info: If we put in E1 a value NOT in the lookup vector, the formula delivers #N/A, which is correct. More details can be found at https://ask.libreoffice.org/en/question/298436/lookup-picks-data-from-unreferenced-sheet/ Version: 7.1.1.2 (x64) / LibreOffice Community Build ID: fe0b08f4af1bacafe4c7ecc87ce55bb426164676 CPU threads: 4; OS: Windows 6.3 Build 9600; UI render: Skia/Raster; VCL: win Locale: en-GB (en_GB); UI: en-US Calc:
Created attachment 170604 [details] LOOKUP formula picks data from unreferenced sheet
In an ideal world, software engineers deliver software free of all kinds of bugs and defects to their clients. Unfortunately, such a world does not exist. 0-bug software is a myth. Bugs are very commons in today's software, and a great part of their lifecycle consists of resolving them. Why? Well, because the error is human. If you are dry as dust with your ugly hairs OR baldness, then I will suggest you to try our Human Hair Wigs Cheap. visit us: https://zaynting.com/collections/human-hair-wigs
Regression introduced by: https://cgit.freedesktop.org/libreoffice/core/commit/?id=a2a15163a6a381957f2cb2ed6fe659577ebde558 author Eike Rathke <erack@redhat.com> 2018-03-05 22:03:27 +0100 committer Eike Rathke <erack@redhat.com> 2018-03-05 22:24:51 +0100 commit a2a15163a6a381957f2cb2ed6fe659577ebde558 (patch) tree 4ea856c3a0e71d3f445d849c0360269982ee4c8e parent 2374028d637886fff1ccca84f57624d2aaa81a95 (diff) Resolves: tdf#116216 array sort order of error values in LOOKUP and MATCH Bisected with: bibisect-linux64-6.1 Adding Cc: to Eike Rathke
I highly doubt that would be the culprit.. earlier versions just resulted in #DIV/0! error instead with those 2;1/{...} lookups (which that change actually was about). Nevertheless I'll take a look.
So.. propagating the #DIV/0! error may have looked like an expected behaviour but it was not. This case with an array as second argument and passing a literal scalar value or a single cell reference as result vector as third argument never worked as expected. Check simply by removing the division so the formula reads =LOOKUP(1;(A$2:A$11=E$1);1) which results (and always did) in first sheet's J1 (which is due to A2:A11 being 9 rows and as the result vector is not a vertical column vector it is assumed to be a horizontal row vector, which is equally wrong of course). Removing Regression keyword. Fwiw, how does Excel behave for this? =LOOKUP(1;1/(A$2:A$11=E$1);1) Is the result error, or the value 1? And what happens for =LOOKUP(1;1/(A$2:A$11=E$1);B2) Error, or result of B3 (if E1 is "k2")?
(In reply to Eike Rathke from comment #6) > So.. propagating the #DIV/0! error may have looked like an expected > behaviour but it was not. This case with an array as second argument and > passing a literal scalar value or a single cell reference as result vector > as third argument never worked as expected. Check simply by removing the > division so the formula reads > > =LOOKUP(1;(A$2:A$11=E$1);1) > > which results (and always did) in first sheet's J1 (which is due to A2:A11 > being 9 rows and as the result vector is not a vertical column vector it is > assumed to be a horizontal row vector, which is equally wrong of course). > > Removing Regression keyword. > > > Fwiw, how does Excel behave for this? > > =LOOKUP(1;1/(A$2:A$11=E$1);1) > > Is the result error, or the value 1? And what happens for In Excel 2010 it gives #N/A > > =LOOKUP(1;1/(A$2:A$11=E$1);B2) > > Error, or result of B3 (if E1 is "k2")? In Excel 2010 it gives 0
0 ?!? For the attached document respectively data identically laid out?
Created attachment 170640 [details] How it looks in Excel 2010
Humm.. and what if there is some value in C3? Is that then chosen?
Argh, C2 I meant.
Further test in Excel: same formula =LOOKUP(1;1/(A$2:A$11=E$1);1) in E4, but text k1 in E1, result is 1 or #N/A error?
(In reply to Eike Rathke from comment #12) > Further test in Excel: same formula =LOOKUP(1;1/(A$2:A$11=E$1);1) in E4, but > text k1 in E1, result is 1 or #N/A error? if I change value in E1 to k1, "=LOOKUP(1;1/(A$2:A$11=E$1);1)" returns 1 and "=LOOKUP(1;1/(A$2:A$11=E$1);B2)" returns "value1"
(In reply to Eike Rathke from comment #11) > Argh, C2 I meant. Nothing, "=LOOKUP(1;1/(A$2:A$11=E$1);B2)" still returns 0. I've also tried by adding value to C3, just in case
Odd.. makes no sense to me. With a value in C2 is the result the same 0 if the formula is re-entered? But =LOOKUP(E1;A2:A11;B2) and k2 in E1 is #N/A?
Two more, both with k3 in E1: =LOOKUP(1;1/(A$2:A$11=E$1);B$2:B$3) =LOOKUP(E$1;A$2:A$11;B$2:B$3)
Btw, the test document is a tad bad because the range A1:A11 is not strictly ascending sorted ("k10" is not greater than "k9" but actually between "k1" and "k2"), though it doesn't change the result in *these cases*. However, data in row 11 should be eliminated and all formulas changed from A2:A11 to A2:A10 and B2:B11 to B2:B10
(In reply to Eike Rathke from comment #15) > Odd.. makes no sense to me. With a value in C2 is the result the same 0 if > the formula is re-entered? > yes, it shows the C2 value if the formula is re-entered > But =LOOKUP(E1;A2:A11;B2) and k2 in E1 is #N/A? no, it's the value in C2 and 0 if nothing is in C2.
(In reply to Eike Rathke from comment #15) > Odd.. makes no sense to me. With a value in C2 is the result the same 0 if > the formula is re-entered? > yes, it shows the C2 value if the formula is re-entered > But =LOOKUP(E1;A2:A11;B2) and k2 in E1 is #N/A? no, it's the value in C2 and 0 if nothing is in C2.(In reply to Eike Rathke from comment #16) > Two more, both with k3 in E1: > =LOOKUP(1;1/(A$2:A$11=E$1);B$2:B$3) > =LOOKUP(E$1;A$2:A$11;B$2:B$3) Both return 'value3'
Thanks for testing, Xisco. That sheds some light on behaviour (and is somewhat congruent with the definition in ODFF 6.14.8 LOOKUP, except that has a wrong constraint on the result having to be a vector and does not take a scalar result into account, I'll submit an erratum there). The C2 value returned only when entering the formula indicates Excel has the same problem with updating values if the return cell is outside of the passed result cell range (as no listeners are set up for that). This behaviour of the LOOKUP function is a mess and its use should be strongly discouraged.
Eike Rathke committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/042dbf83122b14fd1dd32705c8f8b7d65c22f21b Resolves: tdf#141146 Fix LOOKUP in array with result scalar / single reference It will be available in 7.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.
Pending review https://gerrit.libreoffice.org/c/core/+/113113 for 7-1
Verified in Version: 7.2.0.0.alpha0+ / LibreOffice Community Build ID: 3e4eb070787d4d44b3bdc95046e5b231dbbef42b CPU threads: 4; OS: Linux 5.7; UI render: default; VCL: gtk3 Locale: en-US (en_US.UTF-8); UI: en-US Calc: threaded @Eike, thanks for fixing this issue!!
Eike Rathke committed a patch related to this issue. It has been pushed to "libreoffice-7-1": https://git.libreoffice.org/core/commit/a08afaadb6249482a9ca448bde60c618c7eda3bd Resolves: tdf#141146 Fix LOOKUP in array with result scalar / single reference It will be available in 7.1.3. 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/130db636988175f474354412d7222e0a749faf03 tdf#141146: sc_ucalc: fix incorrect test name It will be available in 7.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.
Eike Rathke committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/465b8b0e9ad4b0c9c7701dee2820a99c5d00b5bf Work around assert() in circular reference within group calc, tdf#141146 It will be available in 7.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.
Eike Rathke committed a patch related to this issue. It has been pushed to "libreoffice-7-1": https://git.libreoffice.org/core/commit/4b3b1f01178f4ab079fd2d2c71dcf10753dc2680 Work around assert() in circular reference within group calc, tdf#141146 It will be available in 7.1.3. 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.