Ask Your Question

Vlookup does not work when user interface not english [closed]

asked 2013-06-11 22:40:36 +0200

Ljiljan gravatar image

updated 2013-06-13 11:03:48 +0200

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?

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2015-11-02 05:05:37.478168


Please what is your formula?

m.a.riosv gravatar imagem.a.riosv ( 2013-06-11 23:06:59 +0200 )edit

=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.

Ljiljan gravatar imageLjiljan ( 2013-06-11 23:24:05 +0200 )edit

1 Answer

Sort by » oldest newest most voted

answered 2013-06-11 23:52:58 +0200

Pedro gravatar image

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??? :)

edit flag offensive delete link more


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)

Ljiljan gravatar imageLjiljan ( 2013-06-12 10:49:10 +0200 )edit

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.

Ljiljan gravatar imageLjiljan ( 2013-06-12 10:49:13 +0200 )edit

@Ljiljian, you just found your first bug :) 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())

Pedro gravatar imagePedro ( 2013-06-12 12:29:01 +0200 )edit

@Pedro - 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?

ROSt52 gravatar imageROSt52 ( 2013-06-12 16:17:21 +0200 )edit

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

Pedro gravatar imagePedro ( 2013-06-12 17:51:38 +0200 )edit

Well, I didn't think of this as bug, but it is :) 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.

Ljiljan gravatar imageLjiljan ( 2013-06-12 23:10:03 +0200 )edit

@Pedro - thanks for posting the bug report!

ROSt52 gravatar imageROSt52 ( 2013-06-13 03:09:08 +0200 )edit

Question Tools


Asked: 2013-06-11 22:40:36 +0200

Seen: 2,216 times

Last updated: Jun 13 '13