Zero on the left !!!

[MAANI]

I have cells with numbers like this, 012345678 or 001234567, I formatted all
cells with this function =text(cell,"000000000") so that the zeros are not
cancelled,which is good,my problem was this:
a1=012345678 ,b1=012345678, c1=Shop Stock.
=if(a1=b1,"yes","no") the result was yes.
=if(isna(vlookup(a1,b:c,2,0)),"????",vlookup(a1,b:c,2,0)) the result was
???? and it should be Shop Stock !!!!!!
Could someone help,
Thx

 

[Max]

Try something like this,
with the lookup values in col A formatted in the same way:
=VLOOKUP(TEXT(A1,"000000000"),B:C,2,0)
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:19,000 Files:362 Subscribers:62
xdemechanik

 

[MAANI]

Max,the column that has the numbers is pasted special as values,so your
suggestion didn't work.

 

[Max]

Upload & post a direct link here to your sample* which doesn't work
http://freefilehosting.net/
*desensitized
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:19,500 Files:362 Subscribers:62
xdemechanik

 

[MAANI]

Here is the link http://freefilehosting.net/download/415b3.
Notice column G in Shop Stock sheet is not working

 

[Max]

Notice column G in Shop Stock sheet is not working ..

In Shop Stock,
what you had in G3, array-entered*, copied down:
=IF(ISNA(VLOOKUP($B3,'Local Purchase
$'!$A:$F,3,FALSE)),"",VLOOKUP($B3,'Local Purchase $'!$A:$F,3,FALSE))
*unnecessarily array-entered

Try this correction
In G3, normal ENTER:
=IF(ISNA(MATCH($B3,'Local Purchase $'!$B:$B,0)),"",VLOOKUP($B3,'Local
Purchase $'!$B:$F,2,0))
Copy G3 down

You were matching the lookup value in B3 to the wrong lookup col in the
vlook's table array in: Local Purchase $, ie to col A, instead of to col B.
So of course, you'd get no results.

The correction essentially covers this key error, and suggests the use of
MATCH in the front ISNA error trap & normal ENTER for faster recalc, and it
uses the shorter zero instead of FALSE to specify exact matching

Believe that should do it ok for you
Btw, do take a moment to press the Yes button below, won't you?
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:19,500 Files:362 Subscribers:62
xdemechanik
---

 

[MAANI]

Thanks MAX that helped alot. I did press the YES button.

 

[Max]

Welcome, thanks for feeding back & for the response rating.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:19,500, Files:362, Subscribers:62
xdemechanik