# Vlookup problem

#### Dave T

##### Board Regular
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.

### Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

#### Zack Barresse

##### MrExcel MVP
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?

Replies
21
Views
763
Replies
3
Views
294
Replies
4
Views
173
Replies
2
Views
297
Replies
3
Views
400

1,195,632
Messages
6,010,791
Members
441,569
Latest member
PeggyLee

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back