This is a regression from 3.3.4. When EDITING a Calc cell, if you type FALSE as Sort Order for the VLOOKUP function in 3.3.4 it is automatically converted to zero. This is not expected but it has the same meaning (it is still a small bug, since FALSE is a valid argument) In 3.4.4 (and 3.5.0) typing FALSE is accepted but the function results in #NAME (i.e. it's unusable)
This is not a blocker. It is a normal bug and can't block the release.
(In reply to comment #1) > This is not a blocker. It is a normal bug and can't block the release. It is a functional regression on version 3.3.4 which is the current Enterprise ready release. If this bug is not fixed it will be included in the ONLY Enterprise version available (the upcoming 3.4.5) as a regression. IMO that makes it a blocker: it affects all users and it is a regression.
Indeed! In 3.3.3 for a formula with "VLOOKUP(search_value; search_range; return_column_index; sort_order)" #sort_order' was changed to "0" for a text input "false" and to "1" for a text input "true". This feature seems to be lost after 3.3, but simply typing "0/1" instead of "FALSE/TRUE) heals the problem. And no existing documents will show a problem, after input the sort order string has already been modified. An issue with such a simple workaround definitively is not a blocker. We here have 2 problems: @David: Help for the current version(s) is wrong, on a.m. WIKIHELP page I read "If SortOrder is set to FALSE or zero ...", that's wrong, currently only the numbers will be accepted. Can you please proceed after clarification with Kohei concerning possible fix of problem 2? In "Using Spreadsheets in LibreOffice 3" 16 June 2011 only the Numbers are mentioned, there everything is correct. @Kohei: Is the lost string-to-number feature intended or will it be fixed (or even enhanced to accept the strings)? Please check and clarify with David! @pedlino: Please read blocker definition <http://wiki.documentfoundation.org/Release_Criteria#Blocker_Bug_Definition>
It works for me. Please provide a test document.
(In reply to comment #4) > It works for me. Please provide a test document. There is no point in sending a document. The error occurs when you type FALSE or TRUE. When you press Enter, the cell will show #NAME. If you ignore the error and save the file, when you load it FALSE is converted to 0 so the error doesn't show up again. The problem is during EDITING, as I mentioned.
(In reply to comment #5) > (In reply to comment #4) > > It works for me. Please provide a test document. > > There is no point in sending a document. The error occurs when you type FALSE > or TRUE. When you press Enter, the cell will show #NAME. If you ignore the > error and save the file, when you load it FALSE is converted to 0 so the error > doesn't show up again. I did that and it works for me. That's what I meant when I said "it works for me". I still need a test document to proceed.
Created attachment 54368 [details] Test file for vlookup bug check Here is a test file. I get the same error in 3.4.4 (Build: 402) and LOdev 3.5.0 Build ID: f923851-7f15fca-1f1fd1a-ca8e46d-5bcbce4 under Win XP Pro x86 SP3
And I type in B8: =VLOOKUP($B$6,$A$1:$D$4,2,False) and it gets turned into =VLOOKUP($B$6,$A$1:$D$4,2,0) after hitting ENTER. The formula result shows 2. I even tried =VLOOKUP($B$6,$A$1:$D$4,2,FALSE()) and still get 2 as the result. Never #NAME!.
Just found what the problem is. Because my Regional Settings are in Portuguese but I'm using Windows EN_US and the LO GUI is EN_US it creates and odd mix: the functions are in English but the codes are in Portuguese. I.e. if I type =ISVALUE(A1) and A1 contains text, I get FALSO (not FALSE) Conversely if I type FALSE in the VLOOKUP function I get #NAME, but if I type FALSO, I get 2 as expected. After manually changing the Locale in LO Language settings to EN_US FALSE is working as expected. This is probably a rare combination (using a GUI in a different language than the Locale) so the bug can probably be dismissed as a Known Limitation?
(In reply to comment #9) > Just found what the problem is. > > Because my Regional Settings are in Portuguese but I'm using Windows EN_US and > the LO GUI is EN_US it creates and odd mix: the functions are in English but > the codes are in Portuguese. > > I.e. if I type =ISVALUE(A1) and A1 contains text, I get FALSO (not FALSE) > > Conversely if I type FALSE in the VLOOKUP function I get #NAME, but if I type > FALSO, I get 2 as expected. > > After manually changing the Locale in LO Language settings to EN_US FALSE is > working as expected. > > This is probably a rare combination (using a GUI in a different language than > the Locale) so the bug can probably be dismissed as a Known Limitation? It's working as expected then. Not a known limitation. Either way we should close this bug.
I can confirm pedlino's results concerning locale settings dependency! I only have German 3.3.3, but wanted to do the test in English to get better compatibility. I checked with reporter's sample and "LibreOffice 3.4.4 - German WIN7 Home Premium (64bit) German UI [Build ID: OOO340m1 (Build:402)]" formula is "=SVERWEIS($B$6;$A$1:$D$4;2;0)" typing "wahr" and "falsch", everything works fine, I checked with reporter's sample and "LibreOffice 3.4.4 - German WIN7 Home Premium (64bit) English UI [Build ID: OOO340m1 (Build:402)]" , formula is "=VLOOKUP($B$6;$A$1:$D$4;2;0)", typing "true" and "false" produces ERRO "#NAME", typing "wahr" and "falsch" everything works fine. That has some kind of logic, but also is a little worrying. I will have to think about that, but currently I also think it's not a bug.