vlooup-Can't find existing value - NOT format problem

**EliJB** · 03-24-2008, 03:54 PM

Attachment.

**EliJB** · 03-24-2008, 03:58 PM

I've tried the formula:

=IF(B15="", "", VLOOKUP(TEXT(B15,"0000000"), products, 2, FALSE))

It did not work: The Item UPC is anything from 5 to 7 digits.

**NBVC** · 03-24-2008, 04:01 PM

I've tried the formula:

=IF(B15="", "", VLOOKUP(TEXT(B15,"0000000"), products, 2, FALSE))

It did not work: The Item UPC is anything from 5 to 7 digits.

...yes I noticed..

Try selecting Column B and then go to Data|Text to Columns.

Click Next twice to get to the 3rd window.

Select Text in the top right corner and then click Finish.

Now when you type in a number with a leading zero, it should stay.

Note: You may have to re-type some of your previous entries.

**EliJB** · 03-24-2008, 04:11 PM

It worked!!!!!

Thanks a lot!!!!

So, I guess it WAS a formatting problem.

But why didn't a regular cell formatting work?

I've noticed that If I insert a new row, I have to redo the process.

**NBVC** · 03-24-2008, 04:14 PM

I've noticed that If I insert a new row, I have to redo the process.

I tested this on the sheet you attached and I don't have to redo the process....

It stays text-formatted.

**EliJB** · 03-24-2008, 04:23 PM

Originally Posted by NBVC

I tested this on the sheet you attached and I don't have to redo the process....

It stays text-formatted.

I Highlighted the column and choose Text as my format. Then I re-did Text to Columns. Then every time I inserted a row, the format stayed the same.

But before that,the new row would have a General Format.

Thanks for the help!!!

**NBVC** · 03-24-2008, 04:35 PM

Originally Posted by EliJB

I Highlighted the column and choose Text as my format. Then I re-did Text to Columns. Then every time I inserted a row, the format stayed the same.

But before that,the new row would have a General Format.

Thanks for the help!!!

So problem solved?