Bug 144229 - vlookup does not find value
Summary: vlookup does not find value
Status: RESOLVED DUPLICATE of bug 141989
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Writer (show other bugs)
Version:
(earliest affected)
6.4.7.2 release
Hardware: All All
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks:
 
Reported: 2021-09-01 03:17 UTC by Elmar
Modified: 2022-12-28 20:55 UTC (History)
2 users (show)

See Also:
Crash report or crash signature:

Attachments
vlookup error (41.29 KB, application/vnd.oasis.opendocument.spreadsheet)
2021-09-01 03:18 UTC, Elmar 		Details
View All Add an attachment (proposed patch, testcase, etc.)

Note You need to log in before you can comment on or make changes to this bug.
Description Elmar 2021-09-01 03:17:09 UTC 
I have a table of months (3 letters)
for some reason vlookup does not find Aug and Dec
Comment 1 Elmar 2021-09-01 03:18:36 UTC 
Created attachment 174685 [details]
vlookup error
Comment 2 Mike Kaganski 2021-09-01 03:58:56 UTC 
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
Comment 3 Mike Kaganski 2022-07-03 22:14:32 UTC 
*** Bug 149832 has been marked as a duplicate of this bug. ***
Comment 4 Mike Kaganski 2022-12-28 20:55:51 UTC 

*** This bug has been marked as a duplicate of bug 141989 ***