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

**EliJB** · 03-24-2008, 02:49 PM

I have two columsn: Item UPC numbers and descriptions.

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

I am using the Vlookup formula to pull the descriptions.
I have formatted the cells as Text, because I want to
keep the zeros.

If the UPC starts with zero, there is no problem.
But if I enter an UPC that starts with a non-zero then I get
the Value Not Available Error.
The big exception is item number 13146.I have no problems pulling
the description using that number, even though is formatted just like the others.

In order for the formula to work I have to format the UPC
as "General" on both sheets(for the non-zero UPC). But that's is time consuming. I cannot format the whole column as General, because that eliminates the zero.

I've tried to copy the format of item 13146 unto the other items
But that did not work.

At this point, I am thinking that Excel keeps a number format
even though I set the Format to Text. I am not sure how to test
for this, so right now is just an idea.

Anyone has any ideas, I am willing to try anything at this point!!!

I was going to attach the file, but I can't. Something about Uploading Error:invalid file.

**NBVC** · 03-24-2008, 03:23 PM

Try:

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

there are 7 zeroes in my formula... change to match the number of digits your UPC has including any leading 0

BTW: To upload an Excel file, it must be zipped and under 100kb.

**protonLeah** · 03-24-2008, 03:24 PM

To attach a .xls file, (if you are using Windows OS) in Windows Explorer, right click the file, choose Send to > compressed (zipped) file. Ensure that the zipped file size is <100K. Then attach the zipped file.

**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!!!