[Libreoffice-bugs] [Bug 144229] vlookup does not find value

[bugzilla-daemon at bugs.documentfoundation.org]

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

Mike Kaganski <mikekaganski at hotmail.com> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
         Resolution|---                         |NOTABUG
             Status|UNCONFIRMED                 |RESOLVED

--- Comment #2 from Mike Kaganski <mikekaganski at hotmail.com> ---
You are trying to lookup using a formula like

  VLOOKUP("Aug";tMth;2)

i.e., using *sorted range lookup* [1], in a table with month names which are
unsorted alphabetically. This mode *requires* that you have the first column
sorted. It is documented that if you try to use it on an unsorted column, you
get wrong results.

Note that you not only get #N/A for some moths, but also wrong values for some
others:

  VLOOKUP("Jan";tMth;2) -> 1
  VLOOKUP("Feb";tMth;2) -> 2
  VLOOKUP("Mar";tMth;2) -> 3
  VLOOKUP("Apr";tMth;2) -> 4
  VLOOKUP("May";tMth;2) -> 5
  VLOOKUP("Jun";tMth;2) -> 6
  VLOOKUP("Jul";tMth;2) -> 2 (!)
  VLOOKUP("Aug";tMth;2) -> #N/A
  VLOOKUP("Sep";tMth;2) -> 9
  VLOOKUP("Oct";tMth;2) -> 8 (!)
  VLOOKUP("Nov";tMth;2) -> 8 (!)
  VLOOKUP("Dec";tMth;2) -> #N/A

You must use the fourth argument to VLOOKUP equal to 0 to use on unsorted data:

  VLOOKUP("Aug";tMth;2;0)

[1]
https://help.libreoffice.org/7.2/en-US/text/scalc/01/04060109.html?DbPAR=CALC#bm_id3153152

-- 
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/20210901/9e580eae/attachment.htm>