B
Ben Godber
Hi
I am trying to do a basic, run-of-the mill VLOOKUP. It refers to phone
IMEIs, which are 15 digits long but obviously being serial numbers
have to be retained exactly (ie not rounded), although there doesn't
have to be a calculation performed on them.
=VLOOKUP(E705,Their_Data,6,FALSE)
The data in E705 is the result of this formula:
=IF(LEN('Raw data - mcu'!I704)=15,'Raw data - mcu'!I704,"CHECK IMEI")
and (originally) is in "Number - 0 decimal places" format.
Whenever I try to perform the VLOOKUP on these numbers, Excel merely
returns #N/A. When I swap an IMEI for "14" in both source and lookup
tables the function works as expected. Looking at the "helper" box
that clicking on the "=" button brings up refers to the value in E705
as 3.51640E+14, which obviously it isn't.
Changing the format of the cells makes no difference at all. Changing
them to text converts the number into the E+14 format ("..contents
will be displayed exactly as entered" appears to be an outright lie -
it's using some backroom conversion to display something that I never
once entered)
Is it simply a limitation of my £600 Office package that I can't work
with these IMEIs? I can't believe that the millions of people who work
in the phone industry never use VLOOKUPs involving IMEIs, so must be
me. But what am I doing wrong?
Any ideas very gratefully received - please post here for the benefit
of future generations or email me.
Cheers
Ben
I am trying to do a basic, run-of-the mill VLOOKUP. It refers to phone
IMEIs, which are 15 digits long but obviously being serial numbers
have to be retained exactly (ie not rounded), although there doesn't
have to be a calculation performed on them.
=VLOOKUP(E705,Their_Data,6,FALSE)
The data in E705 is the result of this formula:
=IF(LEN('Raw data - mcu'!I704)=15,'Raw data - mcu'!I704,"CHECK IMEI")
and (originally) is in "Number - 0 decimal places" format.
Whenever I try to perform the VLOOKUP on these numbers, Excel merely
returns #N/A. When I swap an IMEI for "14" in both source and lookup
tables the function works as expected. Looking at the "helper" box
that clicking on the "=" button brings up refers to the value in E705
as 3.51640E+14, which obviously it isn't.
Changing the format of the cells makes no difference at all. Changing
them to text converts the number into the E+14 format ("..contents
will be displayed exactly as entered" appears to be an outright lie -
it's using some backroom conversion to display something that I never
once entered)
Is it simply a limitation of my £600 Office package that I can't work
with these IMEIs? I can't believe that the millions of people who work
in the phone industry never use VLOOKUPs involving IMEIs, so must be
me. But what am I doing wrong?
Any ideas very gratefully received - please post here for the benefit
of future generations or email me.
Cheers
Ben