OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

VLookup Issue

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
ukneil
General User
General User


Joined: 29 Sep 2007
Posts: 37

PostPosted: Sun Apr 29, 2012 6:49 pm    Post subject: VLookup Issue Reply with quote

Hi Guys,

I have two sheets....

Sheet one has my prices in it, and in column A is the item title - B the price - C the category.

In sheet 2 is my system, and I have drop down box that selects the Item, and then I use a lookup on this sheet to put the Price and Categoy in the relvant column.

All my items are 100% different, so there is no confusion there.

Around 75% of the items work fine, but in about 25% of the cases I get a N/A# in the box where the lookup of Price/Category should be.

Oddly, its for BOTH not just Price OR Category.

Can anyone tell me what this is likely to be - its puzzling me!!

Cheers
Neil
Back to top
View user's profile Send private message
ken johnson
Super User
Super User


Joined: 23 Apr 2009
Posts: 2032
Location: Sydney, Australia

PostPosted: Sun Apr 29, 2012 7:45 pm    Post subject: Reply with quote

Do you have this option deselected?...
Allow regular expressions in formulae

If any of the texts being looked up contain any of these characters
(
)
[
\
+
*
?
and if the "Allow regular expressions in formulae" option is selected, VLOOKUP will return #NA!

Go Tools|Options...|OpenOffice.org Calc|Calculate to bring up the appropriate dialogue where you can select/deselect this option.

Ken Johnson
_________________
If your problem has been solved please add "[Solved]" to the beginning of your first post title (edit button).
Back to top
View user's profile Send private message
ukneil
General User
General User


Joined: 29 Sep 2007
Posts: 37

PostPosted: Mon Apr 30, 2012 5:48 pm    Post subject: Reply with quote

Hi Ken,

Thanks for that, it seems to have cleared up some of the issues.

As a question, do you know how the Vlookup 'sees' items.

For example, I have some items as follows;

Quality Cotton Socks Long Black
Quality Cotton Socks Long White
Quality Cotton Socks Long Red

Now, with items like this it still seems to have issues.

Any ideas as to why that could be?

Cheers
Neil
Back to top
View user's profile Send private message
ken johnson
Super User
Super User


Joined: 23 Apr 2009
Posts: 2032
Location: Sydney, Australia

PostPosted: Mon Apr 30, 2012 9:37 pm    Post subject: Reply with quote

Just a single character difference between VLOOKUP's 1st parameter and the lookup table's first column values is enough to result in #NA!.
For example the 1st parameter value could have a trailing space that is not present in what seems to be a matching value in the lookup table's first column (or vice versa).

Ken Johnson
_________________
If your problem has been solved please add "[Solved]" to the beginning of your first post title (edit button).
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Tue May 01, 2012 2:31 am    Post subject: Reply with quote

This is one of the many, many reasons why spreadsheets can not organize data properly. They have never been designed for this purpose even if today's computer users try so desparately to organize data in spreadsheets.

If =VLOOKUP(A1 ; $X$1:$Y$999 ; 2 ; 0) returns #N/A although you believe that it should match at X111 simply test =A1=X111 and the result will be FALSE because A1 does not equal X111.
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group