Vlookup problem

Dave T

Board Regular
Joined
Jun 21, 2005
Messages
93
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
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

Zack Barresse

MrExcel MVP
Joined
Dec 9, 2003
Messages
10,881
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
  3. Web
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?
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,996
Messages
5,834,784
Members
430,322
Latest member
excelnoobnoob

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top