Solved

# limitations of vlookup on a pocket pc excel

Posted on 2002-03-28
Medium Priority
304 Views
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 :(
0
Question by:andyuk
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 7
• 4
• 2

LVL 44

Expert Comment

ID: 6902164
Hi andyuk, you can lookup the available functions

http://casio.monacocorp.co.nz/cassiopeia/eseries/e115/pocketexcelfunctions.htm

HTH:O)Bruintje
0

Author Comment

ID: 6902167
This only shows the parameters for the function i need to know can i search for the above information using it
0

LVL 44

Expert Comment

ID: 6902173
are there any spaces in the cells you lookup? what are the precise values for example? i know they vary.
0

LVL 44

Expert Comment

ID: 6902180
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)),"",VLOOKUP(A18,\$A\$1:\$B\$10,2,0))
0

Author Comment

ID: 6902190
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?
0

LVL 44

Expert Comment

ID: 6902194
is there an error?
0

LVL 13

Expert Comment

ID: 6904615
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(Sheet1!B1,Sheet1!B2:B2))

This should work Andy

Bill
0

Author Comment

ID: 6904697
Hi Bill,

I dont have the offset function available to me in pocket excel. Is there a way round this?

Andy
0

LVL 13

Accepted Solution

WJReid earned 200 total points
ID: 6904883
Hi Andy,

In your lookup formulae are you using the false statement?

e.g. =vlookup(\$A\$1,sheet1!\$a\$1:\$B\$99,2,False)

The false statement should look for an exact match and should stop any errors.

Good luck

Bill
0

LVL 44

Expert Comment

ID: 6922957
Hi Andyuk,

any luck yet?

:O)Bruintje
0

LVL 44

Expert Comment

ID: 7019537
---------------------------
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
---------------------------
0

Author Comment

ID: 7021408
Thanks, sorry for not replying our net connection has been off for about a month
0

LVL 44

Expert Comment

ID: 7021431
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)
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how to import an Outlook PST file to Office 365 using a third party product to avoid Microsoft's Azure command line tool, saving you time.
I was prompted to write this article after the recent World-Wide Ransomware outbreak. For years now, System Administrators around the world have used the excuse of "Waiting a Bit" before applying Security Patch Updates. This type of reasoning to me â€¦
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a â€¦
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custoâ€¦
###### Suggested Courses
Course of the Month9 days, 2 hours left to enroll