Description: In an array with columns A to E and 9 rows, I use lookup to search for the maximum and minimum vale in each of the columns B to E and to report the corresponding text on column A. When using maximum, it works ok. When using minimum, it results on #N/A except for the first evaluated column (B). Steps to Reproduce: 1.Build a (at least) nx3 matrix: e.g. 9x3, where the first column (A) is composed of text cells and the second and third (B and C) of numbers 2.Use formula with lookup function, e.g. in cell B10 use "=LOOKUP(MIN(B1:B9),B1:B9,A1:A9)" 3.Use similar formula for C10:"=LOOKUP(MIN(C1:C9),C1:C9,A1:A9)" Actual Results: Then, the results in cells B10 and C10 are: - B10 -> gets the text in the A-column cell corresponding to the row that has the minimum value among B1:B9, as expected - C10 -> just gets #N/A Expected Results: C10 should behave as B10, given the text in the A-column cell which corresponds to the row where the minimum value from C1:C9 is. Reproducible: Always User Profile Reset: Yes Additional Info: IT evaluates correctly for the first column only. If the matrix has 3 or more columns and trhe funtion is used, then it fails for all columns evaluated but the first. Funny enough it doesn't fails with MAX. It also fails if referred to another cell that reports MIN(C1:C9): e.g "=LOOKUP(C11,C1:C9,A1:A9)", while C11 is "=MIN(C1:C9)". User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:45.0) Gecko/20100101 Firefox/45.0
Please attach an example file. Set to NEEDINFO. Change back to UNCONFIRMED after you have provided the document.
Created attachment 140811 [details] A test case showing the bug The bug appears when using lookup(min(#;#),...) or even if referring to another cell (as used in the file)
it seems it has been the behaviour since LibreOffice 3.3 However, in previous versions at least the first column shows the value but not in 5.3 or later. The behaviour in the first column changed after author Eike Rathke <erack@redhat.com> 2016-02-22 19:20:08 +0100 committer Eike Rathke <erack@redhat.com> 2016-02-22 21:30:28 +0100 commit bad266fa06294f1dacec11ec02dfc6ae4ec8cdc4 (patch) tree 9ec77a0436a1768fbd88d41a76fe2b950f3b59ee parent b3a66ccdd3f4098670b593883602093c4a9e712c (diff) ScQueryEntry::GetSearchTextPtr() with SearchType, tdf#72196 @Eike, I thought you might be interested in this issue...
This is not a bug. For LOOKUP(), the data range to perform the lookup in *has* to be *sorted* strictly ascending. If it is not, then the result is arbitrary due to the nature of search algorithms used. It is only by chance that it seems to work for the max values.