Vlookup does not work when user interface not english

I am trying to use Vlookup function but it always returns “#Name?”

After researching on internet, I discovered that User interface and Locale settings affect this… so when I change “Tools → Options → Language Settings → Languages → User Interface and Locale Settings” to English, formula is working… However, when I switch back locale settings to my locale settings (Bosnian/ Croatian), formula returns “#Name”?"

Is this bug or there is solution?

Please what is your formula?

=VLOOKUP(A1;Sheet2!$A$1:$B$4;2;false) formula is 100% correct because it works when I swich locale settings to English, but does not work in Croatian/ Bosnian.

That is expected and it’s one of Calc’s great features! You can choose the language for the menus and help, the language for the spelling of the contents of the cells AND the language for the formulas.

If you switch the interface to your language then the correct function should be in your own language (e.g. VLOOKUP is PROCV in Portuguese).

However if you prefer to use the English formula functions, open Calc and go to Tools, Options, LibreOffice Calc, Formula and check the “Use English function names” option.

Isn’t LibreOffice fantastic??? :slight_smile:

Thanks… I tried all these and didn’t get expected results… however, I discovered the problem :)) and I am very happy… Libre Office is great. The problem was with the word “false”; so I wrote “0” instead of “false” and it works… so instead of VLOOKUP(A1;Sheet2!$A$1:$B$4;2;false) the correct formula is =VLOOKUP(A1;Sheet2!$A$1:$B$4;2;0)

Thanks… I tried all these and didn’t get expected results… however, I discovered the problem :)) and I am very happy… Libre Office is great.

@Ljiljian, you just found your first bug :slight_smile: In fact what is happening is that although the formulas are in English, LO expects the arguments in the Locale Language (which IMO doesn’t make sense). Therefore you were getting #NAME. Please try with FALSE in your own language (in Portuguese FALSO). It will be converted to 0 (zero) automatically. Alternatively you can use =VLOOKUP(A1;Sheet2!$A$1:$B$4;2;FALSE())

@Pedro1 - I agree to you about the mix of formula in English and arguments in local language. This makes absolutely no sense. When in Options “Use English function names” is flagged all (functions AND arguments) must be in English. Every mix creates confusion and will be a source of troubles.

Is there a bug or enhancement request posted? ( I did not find one.) If so, could you please posted the number here?

@ROSt53 I did submit bug fdo#65671 (forgot to post here, sorry!).

Well, I didn’t think of this as bug, but it is :slight_smile: thanks for submitting it. I was having trouble to find out word “false” in my language (it is “netacno”), so I used 0 instead. But if someone wants to check what is the word “false” in their own language, just type the formula in any cell “=false()” without quotations.

@Pedro1 - thanks for posting the bug report!