Download it now!
Bug 107160 - VLOOKUP gets incorrect Value | Ctrl-Shift-F9 doesn't help
Summary: VLOOKUP gets incorrect Value | Ctrl-Shift-F9 doesn't help
Status: RESOLVED NOTABUG
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
5.3.1.2 release
Hardware: x86-64 (AMD64) Windows (All)
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks: Function-Vlookup
  Show dependency treegraph
 
Reported: 2017-04-14 13:59 UTC by mr_stone
Modified: 2017-07-25 03:41 UTC (History)
2 users (show)

See Also:
Crash report or crash signature:


Attachments
affected file (16.25 KB, application/vnd.oasis.opendocument.spreadsheet)
2017-04-14 14:02 UTC, mr_stone
Details
file afer changing the text in column B (14.51 KB, application/vnd.oasis.opendocument.spreadsheet)
2017-04-14 14:03 UTC, mr_stone
Details

Note You need to log in before you can comment on or make changes to this bug.
Description mr_stone 2017-04-14 13:59:54 UTC
Description:
My problem is similar to Bug 106575.

In sheet A cell C11 must be 1400 instead of 100.
Maybe it has something to do with the search criterion in VLOOUP, because when I change the text in column B the bug is gone.

I try to attach the affected file and the file after changing column B.


If I got it right, in Bug 106575 the problem was solved with Ctrl-Shift-F9.
But that doesn't help in my case.



Actual Results:  
Wrong value in cell C11

Expected Results:
Right value in cell C11


Reproducible: Couldn't Reproduce

User Profile Reset: No

Additional Info:


User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64; Trident/7.0; rv:11.0) like Gecko
Comment 1 mr_stone 2017-04-14 14:02:19 UTC
Created attachment 132564 [details]
affected file
Comment 2 mr_stone 2017-04-14 14:03:23 UTC
Created attachment 132565 [details]
file afer changing the text in column B
Comment 3 m.a.riosv 2017-04-14 14:05:45 UTC
The issue is you have forgot the last parameter to work with no sorted list.
C11: =IF(B11="";"";VLOOKUP(B11;Preisliste.$A$1:$B$50;2;0))
https://help.libreoffice.org/3.6/Calc/Spreadsheet_Functions#VLOOKUP
Comment 4 mr_stone 2017-04-14 14:20:54 UTC
Wow! 2 Minutes after my report I got the answer! You are really fast m.a.riosv!

Thank you, you are right, the last value for the sort order was missing in my formular.

But I thought, for the reason that this value is optional, that I don't need it in this case.
That the search criterion doesn't have to be exactly the same value, I didn't know. (I have learned that right now in your link to the help: "Sorted columns can be searched much faster and the function always returns a value, even if the search value was not matched exactly, if it is between the lowest and highest value of the sorted list.")

That was my confusion.

Thanks for your help!