Page 1 of 1
[Solved] VLOOKUP issues (Err. 502)
Posted: Fri Oct 12, 2012 6:21 pm
by Will653
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:
Code: Select all
VLOOKUP(VALUE(I3),$keyseq.$A$1:$B$36892,2,0)
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?
Re: VLOOKUP issues (Err. 502)
Posted: Fri Oct 12, 2012 6:50 pm
by squenson
An err. 502 is for an invalid argument. I guess that the content of I3 is not a valid number.
[SOLVED] VLOOKUP issues (Err. 502)
Posted: Fri Oct 12, 2012 7:04 pm
by Will653
squenson wrote:An err. 502 is for an invalid argument. I guess that the content of I3 is not a valid number.
You sir would be absolutely correct! Thank you so much! That took a second set of eyes.
Re: VLOOKUP issues (Err. 502)
Posted: Fri Oct 12, 2012 7:06 pm
by Villeroy
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.