[Libreoffice-bugs] [Bug 141989] Calc VLOOKUP from DATA VALIDATION cell returns N/A for two of the validated values

[bugzilla-daemon at bugs.documentfoundation.org]

https://bugs.documentfoundation.org/show_bug.cgi?id=141989

--- Comment #8 from Mike Kaganski <mikekaganski at hotmail.com> ---
(In reply to Colin from comment #7)
> How could it "find" and return the "wrong" location? It couldn't have
> matched the string at that location so I would have expected more N/As

Please read the description of the mode:

> SortedRangeLookup is an optional parameter that indicates whether the first
> column in the array contains range boundaries instead of plain values. In
> this mode, the lookup returns the value in the row with first column having
> value equal to or less than SearchCriterion.

Note that "to or less"! When you have a column of

1
2
3
4
5

doing sorted lookup for 4.75 would give you 4, not N/A. That is very useful,
e.g., for date ranges like "who was the president of USA in 1956?", when
Eisenhower has 1953-01-20 as his start date, and Kennedy has 1961-01-20.

But in case of unsorted range, VLOOKUP still assumes that if it didn't find
perfect match, then the earliest value of the two last checks is the one it was
looking for (it consider it "smaller"). Hence the wrong, but still found,
results.

-- 
You are receiving this mail because:
You are the assignee for the bug.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://lists.freedesktop.org/archives/libreoffice-bugs/attachments/20210430/5bbee49f/attachment-0001.htm>