VLookup Issue

ukneil · General User Joined: 29 Sep 2007 Posts: 37

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

ken johnson · Posted: Sun Apr 29, 2012 7:45 pm Post subject:

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).

ukneil · General User Joined: 29 Sep 2007 Posts: 37

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

ken johnson · Posted: Mon Apr 30, 2012 9:37 pm Post subject:

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).

Villeroy · Posted: Tue May 01, 2012 2:31 am Post subject:

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 http://forum.openoffice.org