Lookup Problem: Text As Search Criterion

BlessedWrath · General User Joined: 05 Aug 2010 Posts: 5

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

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

Perhaps you are not satisfying this condition required by the LOOKUP function...

BlessedWrath · General User Joined: 05 Aug 2010 Posts: 5

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

keme · 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>)

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

BlessedWrath · General User Joined: 05 Aug 2010 Posts: 5

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

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

BlessedWrath · General User Joined: 05 Aug 2010 Posts: 5

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

Villeroy · 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.

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

BlessedWrath · General User Joined: 05 Aug 2010 Posts: 5

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

keme · 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.