Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Zero on the left !!!
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Zero on the left !!!
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" wrote: 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 !!!!!! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Zero on the left !!!
Max,the column that has the numbers is pasted special as values,so your
suggestion didn't work. "Max" wrote: 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" wrote: 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 !!!!!! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Zero on the left !!!
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" wrote in message ... Max,the column that has the numbers is pasted special as values,so your suggestion didn't work. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Zero on the left !!!
Here is the link http://freefilehosting.net/download/415b3.
Notice column G in Shop Stock sheet is not working "Max" wrote: 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" wrote in message ... Max,the column that has the numbers is pasted special as values,so your suggestion didn't work. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Zero on the left !!!
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 --- |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Zero on the left !!!
Thanks MAX that helped alot. I did press the YES button.
"Max" wrote: 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 --- |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Zero on the left !!!
Welcome, thanks for feeding back & for the response rating.
-- Max Singapore http://savefile.com/projects/236895 Downloads:19,500, Files:362, Subscribers:62 xdemechanik --- "MAANI" wrote in message ... Thanks MAX that helped a lot. I did press the YES button. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to make left side stay still and right side can move left to r | Excel Discussion (Misc queries) | |||
My Excel view is Right to Left instead of Left to Right !!! | Excel Discussion (Misc queries) | |||
worksheet is right-to-left but i want left-to-right...? | New Users to Excel | |||
Column labels run right to left, not left to right | Excel Discussion (Misc queries) | |||
How to change the right-to-left worksheet to left-to-right workshe | Excel Discussion (Misc queries) |