Thread: Formatting Cells Problem producing error in my vlookup's

I uploaded this file to help show you the problem,

Currently i have the whole column formatted to text, . All my vlookups work except for a sporadic few ( not sure why ). On the ones that dont work it displays N/A in my adjacent cell's which contain vlookup formula's and i have to format the cell to a number for it to work. I need a way to format the whole column to where it works on every vlookup. I have thousands of row's and its tedious finding every N/A and converting it to a number.

So again having everything formatted to text produces errors in some vlookup's,

If i format everything to a number it works but i lose the zero's which cause errors / na's in every part number that lost a zero.

You don't show the formula or the lookup table in your sample so difficult to diagnose...

...but the first column of the lookup table should also be text formatted... so that 10381 is a text string in both rows.

Not sure why it isn't working for you. Like NBVC said, make sure that the lookup number is formatted as text, so it could be:

=VLOOKUP("10381",A3:A16,1,0)
or
=VLOOKUP(text(A9,"00000"),A3:A16,1,0)

Most likely you would want to use the latter -- format the cell that you are looking up in addition to the array (which you already did).

You need to format all the cells in column A as text. The cell you're looking up and the cell you are looking at need to be the same format (text or value). The cell you're looking up is text and some of the column A cells are General (so they default to value).

Oh ya, you have to go to each cell in the column and press F2 then Enter for the formatting to really change. It's really annoying but for some reason Excel requires it. Basically you have to edit the cell then press enter (even if you don't change anything). You may also be able to copy and paste for the same effect. The formatting change won't take place until the cells are edited.

Originally Posted by donnydorko

when i do that in my new example file it still shows the N/A..

If you select Column A and go to Data tab, then click Text to Columns.

Skip to the 3rd window and select Text from the Column Data Format area.. then click Finish...

That should convert all entries in column A to text.

Nice to hear

Thanks for reporting that.