andyuk
asked on
limitations of vlookup on a pocket pc excel
I am using the vlookup command in pocket excel and need to know the limitations. I am trying to do the following
Sheet1
A1 | A2
*2345 5421* | BaseUnit
92299 99 | Laptop
T1178 9922 | BaseUnit
Z9172223 2312| Monitor
Sheet 2
A1 | A2
| vlookup(a1,sheet1!a2:b99,2 )
It does not seem to like certain values, these values are meant to be barcodes so the format can vary :(
Sheet1
A1 | A2
*2345 5421* | BaseUnit
92299 99 | Laptop
T1178 9922 | BaseUnit
Z9172223 2312| Monitor
Sheet 2
A1 | A2
| vlookup(a1,sheet1!a2:b99,2
It does not seem to like certain values, these values are meant to be barcodes so the format can vary :(
ASKER
This only shows the parameters for the function i need to know can i search for the above information using it
are there any spaces in the cells you lookup? what are the precise values for example? i know they vary.
it still works as in real excel
=VLOOKUP(A18,$A$1:$B$10,2, 0)
gives #N/A on no match which can be catched with
=IF(ISERROR(VLOOKUP(A18,$A $1:$B$10,2 ,0)),"",VL OOKUP(A18, $A$1:$B$10 ,2,0))
=VLOOKUP(A18,$A$1:$B$10,2,
gives #N/A on no match which can be catched with
=IF(ISERROR(VLOOKUP(A18,$A
ASKER
999999-99999-999999 1
888888-88888-888888 2
777777-77777-777777 3
666666-66666-666666 3
for the lookup data
then thr formula would be
A1 | B1
| =vlookup(A1,sheet1!$a$1:$b $99,2)
| =vlookup(A2,sheet1!$a$1:$b $99,2)
| =vlookup(A3,sheet1!$a$1:$b $99,2)
hope this helps?
888888-88888-888888 2
777777-77777-777777 3
666666-66666-666666 3
for the lookup data
then thr formula would be
A1 | B1
| =vlookup(A1,sheet1!$a$1:$b
| =vlookup(A2,sheet1!$a$1:$b
| =vlookup(A3,sheet1!$a$1:$b
hope this helps?
is there an error?
Hi Andy,
Have you thought of trying the index function instead:
Sheet1
Column A Column B
number name
*2345 5421* BaseUnit
92299 99 Laptop
T1178 9922 BaseUnit
Z9172223 2312 Monitor
Sheet 2
A1: Drop down list with item numbers
A2:=OFFSET(Sheet1!A1,MATCH (A1,Sheet1 !A2:A99,0) ,MATCH(She et1!B1,She et1!B2:B2) )
This should work Andy
Bill
Have you thought of trying the index function instead:
Sheet1
Column A Column B
number name
*2345 5421* BaseUnit
92299 99 Laptop
T1178 9922 BaseUnit
Z9172223 2312 Monitor
Sheet 2
A1: Drop down list with item numbers
A2:=OFFSET(Sheet1!A1,MATCH
This should work Andy
Bill
ASKER
Hi Bill,
I dont have the offset function available to me in pocket excel. Is there a way round this?
Andy
I dont have the offset function available to me in pocket excel. Is there a way round this?
Andy
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Hi Andyuk,
any luck yet?
:O)Bruintje
any luck yet?
:O)Bruintje
-------------------------- -
Hi Andyuk,
Any luck yet to try the suggestions in this thread?
Please provide some feedback to the experts willing to spend their time and try to solve your problem.
-------------------------- -
Thanks
:O)Bruintje
-------------------------- -
Hi Andyuk,
Any luck yet to try the suggestions in this thread?
Please provide some feedback to the experts willing to spend their time and try to solve your problem.
--------------------------
Thanks
:O)Bruintje
--------------------------
ASKER
Thanks, sorry for not replying our net connection has been off for about a month
do i sound so harsh ? :)
no sorry needed was just routine round of open Q's to see if people needed more help or moved on
thanks for finalizing
(i'm going to work on those scripts)
no sorry needed was just routine round of open Q's to see if people needed more help or moved on
thanks for finalizing
(i'm going to work on those scripts)
http://casio.monacocorp.co.nz/cassiopeia/eseries/e115/pocketexcelfunctions.htm
HTH:O)Bruintje