Apache OpenOffice (AOO) Bugzilla – Issue 104241
ODFF: LOOKUP, MATCH, VLOOKUP, HLOOKUP in a range lookup should not find last numeric value if queried for string
Last modified: 2017-05-20 11:11:10 UTC
LOOKUP, MATCH, VLOOKUP and HLOOKUP in a sorted range lookup are defined to return the last value <= query. However, if the query is string, currently the last numeric value is returned if no string <= query is present in the range. Instead, an error is to be returned, as is defined in ODFF/OpenFormula and does Excel. For example, looking up "a" in the sequence {1,3,"b","d"} currently returns a match for 3; it should not, but return #N/A instead.
Created attachment 64176 [details] testcase
oops - hope this was not ment serious, changing sorting-paradimas might be done somewhere else. However, this never could be a defect, nor is this related to the subcomponent programming. If this is the pledge for having a (NEW) parameter in these features for finding exact match, which returns a "N/A" if it does not exist, that is okay, and welcome. But touching the default behaviour is not welcome. Bytheway: The example is not that serious, as it consists only of descrete values of strings. But very offen these functions are used to group values, like group of "1 2 3 4 5" and "what is the indexed value for 1,25" And bytheway: since years I worry about the wrong searchorder in all these functions: Example: Having a telephonebook and looking for "B" gives should give the first of the B's, not the last of the As like in OO.o, but that is another story Martin
=>er well, I reread your issue, and right now I am uncertain about your complaint, probably we two mean the same, but ... Your example consists of a data-type-mix, which is normally unwanted in a situation you describe. So, would you mind to explain it again Martin
@mhatheoo: > Your example consists of a data-type-mix, which is normally unwanted in > a situation you describe. This issue is _only_ about mixed sorted data types. Querying for a string in such mixed range should not return the last numeric value if no string less or equal than the query was found. Take a look at the attached testcase document, querying for "a" should return #N/A.
Won't be able to change this in time for OOo3.2 branch off date. Retargeting to OOo3.3
set target to 3.x since not release relevant for 3.4.
Reset assigne to the default "issues@openoffice.apache.org".