Zero Values - vlookup

[taup41]

Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel When using Vlookup i get #NA returned in cells that have a number with a 0 in front. ie: 04011 returns #NA. I then try and type the number 04011 in the cell and find the 0 vanishes but the correct Vlookup result is returned.
I need the 0 to remain in the lookup value cell as it should be 04011, how do i get the 0 to remain when typed?
Cell formatting is GENERAL and when I change to NUMBER the correct result is returned however the 0 vanishes from the lookup value.
SHOW ZERO VALUES is set in my preferences.

Thanks

 

[John McGhie]

You have two choices:

1) If the value is a number, use a Custom format of #,000.00

2) If the number is "Text" change the cell format to Text and type an
apostrophe (') before the characters. Excel will then store the number as
text, and every character you type will appear. VREF will work, but cannot
use text in calculations.

Hope this helps

Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel When
using Vlookup i get #NA returned in cells that have a number with a 0 in
front. ie: 04011 returns #NA. I then try and type the number 04011 in the cell
and find the 0 vanishes but the correct Vlookup result is returned.
I need the 0 to remain in the lookup value cell as it should be 04011, how do
i get the 0 to remain when typed?
Cell formatting is GENERAL and when I change to NUMBER the correct result is
returned however the 0 vanishes from the lookup value.
SHOW ZERO VALUES is set in my preferences.

Thanks

--

The email below is my business email -- Please do not email me about forum
matters unless I ask you to; or unless you intend to pay!

John McGhie, Microsoft MVP (Word, Mac Word), Consultant Technical Writer,
McGhie Information Engineering Pty Ltd
Sydney, Australia. | Ph: +61 (0)4 1209 1410 | mailto:[email protected]