Hello all. We have a file here that has a working VLOOKUP formula that can not be duplicated via copy paste. Hoping some here can help.
We have a spreadsheet A with data that needs to have a value looked up on the column in that spreadsheet A and then matched the value of a second spreadsheet B to return data from a column on spreadsheet A.
The formula that works in the first spreadsheet is this:
How is it possible that I have duplicated the data from both of these spreadsheets and only get Err:502? The data in question is alpha numeric. Can anyone shed some light on this?
Last edited by Will653 on Fri Oct 12, 2012 7:04 pm, edited 1 time in total.
The user specific locale is one reason why 2 spreadsheet programs or even 2 installations of the same program do not accept the same formula string.
You can simply open the same file in the other program and if it works you will see the correct syntax in that program.
A spreadsheet where the comma is used as decimal separator can not use comma as list separator and your formula would be:
=VLOOKUP(VALUE(I3) ; $keyseq.$A$1:$B$36892 ; 2 ; 0)
In Excel the formula would be:
=VLOOKUP(VALUE(I3) , keyseq!$A$1:$B$36892 , 2 , 0)
or
=VLOOKUP(VALUE(I3) ; keyseq!$A$1:$B$36892 ; 2 ; 0)
Without $ in front of the sheet name, with ! as sheet separator and either , or ; as list separator, depending on the locale version of MS Excel.
The VALUE conversion from string to number may fail for the same reason.
VALUE("1.234") returns different values in 2 language versions of Excel or between 2 different setups of LibreOffice.
VALUE("1.23") may return something in one spreadsheet, something else in another one and some error in a third.
The solution for the second problem is: Do not allow any strings where numbers are required.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
