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

Lookup Problem: Text As Search Criterion

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


Joined: 05 Aug 2010
Posts: 5

PostPosted: Thu Aug 05, 2010 5:30 pm    Post subject: Lookup Problem: Text As Search Criterion Reply with quote

I am attempting to use the Lookup function to reference a word, instead of a number, as the search criterion, in order to return a separate numerical value associated with the word.

As an example:

Apple: 2
Pie: 5
Doggy: 8

I should be able to reference the numerical value in each entry on my list, based on the words I have associated them with. However, the problem is that one of those words (out of six) returns "N/A" instead. Every other value in the formula works correctly.

I should mention that the only word in the entire formula which is misbehaving starts with the same letter as another of the six referenced words. I thought it might be possible that the program was not looking any farther than the first letter of each reference, so I researched the possibility of an option which would force recognition of the exact cell contents.

That option is already enabled.

So, my question is this: Is the function fundamentally flawed, or is there another option which needs to be enabled/disabled before it will work properly?
_________________
"The fastest path to Defiance is the demand for Obedience." ~Blessed Wrath
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: Thu Aug 05, 2010 7:00 pm    Post subject: Reply with quote

Perhaps you are not satisfying this condition required by the LOOKUP function...
Calc Help file on LOOKUP function wrote:
Additionally, the search vector for the LOOKUP must be sorted in ascending order, otherwise the search will not return any usable results.

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
BlessedWrath
General User
General User


Joined: 05 Aug 2010
Posts: 5

PostPosted: Thu Aug 05, 2010 8:47 pm    Post subject: Reply with quote

Then, assuming that the two words with identical beginning letters are not in alphabetical order, the second word would return nothing?

Interesting. I may have to alter my list.

Thank you. I'll try altering the list, and see what happens.

EDIT: I altered the word in question, changing it to "z" to ensure that it is the last in order, but the problem has only been partially solved.

Instead of returning N/A, it now returns 3, when it should be 1. I can find no other source of output to the return field, so it must be a problem with the formula itself.

I'm not sure what to do now.
_________________
"The fastest path to Defiance is the demand for Obedience." ~Blessed Wrath
Back to top
View user's profile Send private message
keme
Moderator
Moderator


Joined: 30 Aug 2004
Posts: 2910
Location: Egersund, Norway

PostPosted: Fri Aug 06, 2010 1:05 am    Post subject: Reply with quote

If you have the values listed as indicated, the VLOOKUP() function will work for looking up names and returning numbers (VLOOKUP() requires that the search term be the leftmost column of the range). Make sure you specify the last (optional) parameter to zero, to indicate "unsorted list".
=VLOOKUP(<search term>;<range>;<return column>;0)

If you need a "reverse lookup" (picking the return value to the left of the searched field), you can use MATCH() to determine the row number, then pick the return value with INDEX().
=INDEX(<range>;MATCH(<search term>;<search vector>;0);<return column>)
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Fri Aug 06, 2010 9:37 am    Post subject: Reply with quote

ken johnson wrote:
Perhaps you are not satisfying this condition required by the LOOKUP function...
Calc Help file on LOOKUP function wrote:
Additionally, the search vector for the LOOKUP must be sorted in ascending order, otherwise the search will not return any usable results.

Ken Johnson


Even with sorted lookup values, LOOKUP does not work in "database mode". It won't lookup distinct values.
=LOOKUP(3;{2;4;6} returns a false positive 2.
All functions [V/H]LOOKUP and MATCH assume "spreadsheet mode" where you lookup some value on a numeric scale rather than discrete values.
As keme points out, V/HLOOKUP and MATCH can be switched to "database mode" but the problem remains that all spreadsheets work very badly with text values. In Calc one should turn off regular expressions in formulas (unless you really know why and how). And you should carefully consider the other text related options "case sensitive" and "match whole cells".
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
BlessedWrath
General User
General User


Joined: 05 Aug 2010
Posts: 5

PostPosted: Fri Aug 06, 2010 12:05 pm    Post subject: Reply with quote

Tried VLookup, with specified sort order. Now I get "wrong data type" as an error message.
_________________
"The fastest path to Defiance is the demand for Obedience." ~Blessed Wrath
Back to top
View user's profile Send private message
keme
Moderator
Moderator


Joined: 30 Aug 2004
Posts: 2910
Location: Egersund, Norway

PostPosted: Fri Aug 06, 2010 1:34 pm    Post subject: Reply with quote

BlessedWrath wrote:
Tried VLookup, with specified sort order. Now I get "wrong data type" as an error message.
Show us your formula.
Back to top
View user's profile Send private message
BlessedWrath
General User
General User


Joined: 05 Aug 2010
Posts: 5

PostPosted: Sun Aug 08, 2010 9:56 am    Post subject: Reply with quote

The only real way to do that would be to post the entire document.

I'm certain there are others who have had this problem, and that there are tutorials covering it. I just can't believe that they would have left out the ability to call text instead of numbers. It seems so basic to me.
_________________
"The fastest path to Defiance is the demand for Obedience." ~Blessed Wrath
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Mon Aug 09, 2010 12:43 pm    Post subject: Reply with quote

Most people do not understand how LOOKUP uses to work in Excel and in any other spreadsheet since the 80ies.

MS wrote:
If LOOKUP cannot find the lookup_value, it matches the largest value in lookup_vector that is less than or equal to lookup_value.

http://support.microsoft.com/?scid=kb%3Ben-us%3B324986&x=9&y=17
This type of lookup must fail when the lookup scale is not sorted in ascending order and it matches within scale intervals where most people would expect no match at all:

=LOOKUP(1.5 ; {1;2;3} ; {"A";"B";"C"}) returns "A" since 1.5 is between 1 and 2.

Same in textual context:
=LOOKUP("Ax" ; {"A";"B";"C"} ; {1;2;3}) returns 1 since "Ax" is between A and B.
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
keme
Moderator
Moderator


Joined: 30 Aug 2004
Posts: 2910
Location: Egersund, Norway

PostPosted: Mon Aug 09, 2010 1:47 pm    Post subject: Reply with quote

BlessedWrath wrote:
... call text instead of numbers. It seems so basic to me.
It is. People use it all the time.

However, using those lookup functions takes some practice. There may be some small error in your use of VLOOKUP(). If you will just humor me, post one formula that uses the VLOOKUP() function and fails.

If possible, it could also be useful to see a short segment of your list around a spot where you expect a match. That may not be required, though.
Back to top
View user's profile Send private message
BlessedWrath
General User
General User


Joined: 05 Aug 2010
Posts: 5

PostPosted: Tue Aug 10, 2010 8:53 am    Post subject: Reply with quote

The formula is identical across six separate search criteria, and was copied directly from the original cell. I used absolute references where appropriate, to prevent "shifting" of referenced cells, so that isn't the issue either.

I know exactly what the problem is, but not how to get around it. This is not an issue of a mistyped formula. It is an issue of the program not recognizing the sixth cell as being a valid reference, and there is simply no reason for it.

The fact that the other six formulae worked perfectly is proof that the formula is valid. The only cell which misbehaves is one which is labeled with a word beginning with the same letter as a previous cell. That cannot be a coincidence. As a test, I will replace all six cells with numbers, to prove that the issue is not in the formula.

I had thought of another way to solve the problem, though it requires the elimination of the arrays involved. Since the search criteria are fixed, it isn't exactly necessary to search all six cells to find the appropriate value. If I limit my search to just one cell, I can hopefully evade the sorting issue altogether.

I will post my findings.
_________________
"The fastest path to Defiance is the demand for Obedience." ~Blessed Wrath
Back to top
View user's profile Send private message
keme
Moderator
Moderator


Joined: 30 Aug 2004
Posts: 2910
Location: Egersund, Norway

PostPosted: Tue Aug 10, 2010 12:59 pm    Post subject: Reply with quote

It may be useful to look for leading/trailing spaces in your text cells. (Most typos are apparent, but spaces are invisible.)

If you specify a sorted table to your lookup function, a leading space in a lookup value or a trailing space in the first item in the key column are the most likely causes for the #N/A error. Other "stray" spaces may cause the function to return an unexpected result, but not an error message.

If you specify an unsorted table to your lookup function, any stray space will cause a mismatch (returning the #N/A error), because the "unsorted" function call also requires an exact match.
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