LOOKUP picks data from unreferenced sheet

English
, ,
#1

I am aware that LOOKUP needs a sorted data range, but the formula I use does NOT reference the other sheet, but it picks the data from there! (Please ignore the fact that the formula doesn’t really make sense, but it does not throw an error, so the syntax seems correct.)

There was no cut & paste involved, so I don’t think this is related to Bug 121002

Starting from an empty sheet still has the problem.

I also think it has nothing to do with Bug 128329, but I tried turning of multi-threaded calculation anyway and have even tried the latest fresh version.

Please see the attachment for more details:
2021-03-14 LOOKUP bug.ods

Am I missing something or is this indeed a LOOKUP bug?

#2

That clearly looks like a bug. Please submit at https://bugs.documentfoundation.org/ and attach the sample file to the bug. Thanks.

#3

I’m neither a developer nor otherwise familiar with the core code.
However I was curious concerning this issue, and did some “research” by reworking the Calc document you provided.
My conclusion is that there actually should be supposed a bug in the LOOKUP() (-related) code.

You (OP) may check the attached document, and then report the bug.
ask298436lookup_1.ods
Please link to your report here.

I made the reworked demo with V 7.1.0.4 (portable, 32-bit on Win 10) and also tested with some older versions I keep for the purpose.
The bug seems to be a regression introduced with V 6.1.

#4

Seems with such 0+F3:F10 is looking on the first sheet, adding a new sheet before, test.i3 gives nothing as result.

#5

Ok, I have filed 141146 . Thanks for the follow-up.

#6

Well, as you say, LOOKUP() needs a sorted search vector. Without it, the return value is not predictable. Behavior with unsorted search vector is undefined. Or as the help pages say: “… will not return any usable results.”

A bug means “does not work as specified”, but LOOKUP() is not specified to work with given data, so I’d say it is not a bug. Unexpected, yes, but not a bug.

When source and search vectors are both calculated, they exist “off the grid” as it were. I guess that they reside in temporary work area for the duration of formula evaluation. When matching the value to the unsorted search vector, result is picked “off off the grid”, which in some cases may happen to be inside the grid again, or a different grid, or the holodeck of Starship Enterprise. We cannot tell. It is undefined.

#7

However, “unspecified” shouldn’t be seen as a charter to return a gravely misleading anything.
In addition general ODF specifications for spreadsheet functions tell that received errors (probably one of many error-values) should propagate to the output if not more specialized specification tells otherwise.
See Open Document Format for Office Applications (OpenDocument) Version 1.3. Part 4: Recalculated Formula subchapter 4.6.
Insofar the issue doesn’t depend on how -if at all- the Searchad array is sorted.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.