Created attachment 93499 [details] Examples of all bugs described Lookup function is horribly broken and I question your QA process because of it (but constructively as I am invested in your success). There is a lot of good work here but if the function returns garbage and random results it calls into question the entire suite. It is critical that you fix this as soon as possible, correctly. Lookup works when it wants to, then it returns #N/A randomly when it should not, but even worse than that, it randomly returns values not related to a match including values for non-matches. Please note that I have found the same behavior in OpenOffice 4.0 - so its in the older common code. In the attached sheet you will find 7 lookup functions each identical in size and pointing to their own range of data. They are structured as follows: Lookup values (e.g.: cell B1, G1, L1) are provided, the function returns its result in cell D1 (and I1, N1, D8, I8 etc). The ranges that are looked up are under the Lookup text (example A3:A6) and the values returned are one column to the right of that (example B3:B6). The behaviors to test require that you enter in the lookup cell each value from the lookup range in turn (try them all please, there are only 4 for each example). The search result should match the related search result. For each example, also try entering no value in the lookup field (should give a #N/A) and try entering a value that is not found (should five a #N/A as well). For lookups on B1, everything works as it should to find values in the range (0-3) but a not-found value (4) returns a 0 - which is wrong. For lookups on G1, which is the same data range values as B1 but in reverse (3-1) values 1 and 0 return a #N/A. For lookup on L1, the value x returns #N/A correctly but the value 6 returns 0 - it should also return a #N/A. For lookup on B8, we use text values (a-d) instead of numbers, and everything works. Blank values correctly return a #N/A but a not-found value returns a 0. For lookup on G8 we just reversed the sequence on the search range (d-a), and now a return a #N/A even though it exists. Non-existent values return a 0.
Hi Carlos, thanks for reporting. As you can see in the help LOOKUP() function needs the search vector sorted ascending. LibreOffice help. " LOOKUP Returns the contents of a cell either from a one-row or one-column range. Optionally, the assigned value (of the same index) is returned in a different column and row. As opposed to VLOOKUP and HLOOKUP, search and result vector may be at different positions; they do not have to be adjacent. Additionally, the search vector for the LOOKUP must be sorted ascending, ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ otherwise the search will not return any usable results. " So I think it is not a bug, works as intended. But seems that when the search vector it is not sorted, works except for the last cell in the search vector. Maybe it is not intended but a bit confused.
Ahhh - RTFM. Thank you very much.