Vlookup problem

[Dave T]

Hello All,

I have an Excel 2000 spreadsheet where various three digit cost codes are used that have zeros in front of the numbers. When 001 is entered it is interpreted as a number and is displayed as 1, but users have formatted the cells as text to get it to display 001. Values entered as say 203 are OK.
I have tried using a custom number format for the input cell for the VLOOKUP but get an error. Is it possible to to have an input cell that is either text of a number??

I suppose it will be easier to convert and the codes to numbers using Edit>Copy then Edit>Paste Special and select the Add option and then apply a custom number format to show the zeros.

Thanks in advance

 

[Zack Barresse]

Hello DaveT, welcome to the board!

There are two things you can do here, and it really depends on what you want to use it for as to which you choose.

1) Change the format.

We can change the format to custom 000, which means it will always show three characters whether they are in the value or not. In other words, enter 1 in that cell and it will show 001.

The downside to this is, it's just a format, a mask, a fake. That is not the actual value of the cell. The value of the cell is 1, which can be seen in the formula bar when the cell is selected. So any dependent calculation(s) from this cell that looks at the cell value is only going to see 1, even though you see 001.

Hint: You can use the TEXT function if you are looking to use this number (i.e. 001) in a VLOOKUP or likewise function. Example ..

=VLOOKUP(TEXT(A1,"000"),B1:C100,2,0)

2) Enter the cell as text.

To enter any cell data as text, preceed it with a single apostrophe ( ' ). This signifies to Excel that all data in the cell is text and will not be seen any other way. So you would enter ..

'001

Now you would not see the apostrophe when looking at the cell, but it would appear in the formula bar. This will give you the advantage of looking at the cell value as 001 rather than 1, as it's now textual. The downside is if you try to use that in any precedent calculations it will not be a number (1) but text ("001"). There are ways to coerce this value into a numeric again.

Now, all that being said, you need to decide what your course of action shall be. I'm unfamiliar with your data structure, layout, design and purpose, so I shall refrain from saying anymore.

Does this help?