View previous topic :: View next topic 
Author 
Message 
BlessedWrath General User
Joined: 05 Aug 2010 Posts: 5

Posted: Thu Aug 05, 2010 5:30 pm Post subject: Lookup Problem: Text As Search Criterion 


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 


ken johnson Super User
Joined: 23 Apr 2009 Posts: 2032 Location: Sydney, Australia

Posted: Thu Aug 05, 2010 7:00 pm Post subject: 


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 


BlessedWrath General User
Joined: 05 Aug 2010 Posts: 5

Posted: Thu Aug 05, 2010 8:47 pm Post subject: 


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 


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

Posted: Fri Aug 06, 2010 1:05 am Post subject: 


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 


Villeroy Super User
Joined: 04 Oct 2004 Posts: 10106 Location: Germany

Posted: Fri Aug 06, 2010 9:37 am Post subject: 


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 


BlessedWrath General User
Joined: 05 Aug 2010 Posts: 5

Posted: Fri Aug 06, 2010 12:05 pm Post subject: 


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 


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

Posted: Fri Aug 06, 2010 1:34 pm Post subject: 


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 


BlessedWrath General User
Joined: 05 Aug 2010 Posts: 5

Posted: Sun Aug 08, 2010 9:56 am Post subject: 


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 


Villeroy Super User
Joined: 04 Oct 2004 Posts: 10106 Location: Germany

Posted: Mon Aug 09, 2010 12:43 pm Post subject: 


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%3Benus%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 


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

Posted: Mon Aug 09, 2010 1:47 pm Post subject: 


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 


BlessedWrath General User
Joined: 05 Aug 2010 Posts: 5

Posted: Tue Aug 10, 2010 8:53 am Post subject: 


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 


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

Posted: Tue Aug 10, 2010 12:59 pm Post subject: 


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 


