Greetings.
I run Windows XP and Microsoft Office 2007. I don't think further specs will be pertinent to this situation.
I do not know a lot of database or spreadsheet jargon, so I may use confusing words.
I am beginning to think that LOOKUP might not be what I need to do what I am trying to do. All I want is for it to look at a cell reference, use the word therein to match up to the same word in another list, and then return with the adjacent number in the same row.
I do not explain things well, so I will provide pictures as attachments. The words were all replaced with random ones for the purpose of these screen shots for privacy reasons. They may also be blurry because I saved them as jpegs set to high compression to lower their file sizes.
Anyhow, for the purposes of these images, I use the formula:
=LOOKUP(A35,DummyList,Sheet4!B3:B24)
Where A35 is assigned a word from the list on Sheet4 via a drop-down. The list is called DummyList. I then intend it to match the word in A35 to the word in the list on Sheet4, and then return the corresponding number. In this example, I selected the words Salt (in A33, and yes, the upper one is LOOKUP(A33....) and Forget. Beside them they should return the numbers 1.1 (Salt) and 2.1 (Forget). Instead I get 6.0 (Salt, and 1.0 (forget).
If I do it the other way, where it looks up the number and then returns the corresponding word, it is usually bang-on. Here it gets the wrong number, and doesn't give me an error.
Which brings me to the conclusion that LOOKUP is not going to do what I had hoped it would do, and I don't understand all of the functions, even after reading some of the help files.
Can someone further explain the mechanics of LOOKUP and hopefully suggest something that will be more accurate? I need 'find word, retrieve adjacent cell', not 'find number, retrieve adjacent cell'.
I appreciate anyone who takes their time to read this, and apologize for my round-about way of typing and getting to the point. I've done searches, but could not come up with much help.
[recovering disk space, attachment deleted by admin]