Excel 2007 LOOKUP by name

Greetings.

(Excuse any seemingly random asterisks. I had typed this in a another program then pasted it here, and for some reason, it was strewn with them. I did my best to remove them, but I may have missed a few.)

I run Windows XP and Microsoft Office 2007.

I do not know a lot of database or spreadsheet jargon, so I may use confusing words. I also have used Microsoft excel, up until now, for very basic things like simple arithmetic and whatnot, and just floundered around in the help files to find something that might work for a more complicated project. LOOKUP seemed promising.

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 there are only so many threads and articles I can sift through before I get overwhelmed and just want to give up.

Hello & Welcome to the Forum,

See if this helps and the actual workbooks are always much better than pictures. We can not test possible outcomes on pictures...

http://www.contextures.com/xlFunctions02.html

Instead of pictures, how about a sample workbook that we can Actually manipulate the data, I know that you WILL get more responses with that approach
Include (edited for privacy/confidentiality/etc...must have same formatting and types of data) what you HAVE, what you would EXPECT to see ( preferably in a separate sheet and some notes explaining how the results are obtained (ie.. the "Logic" for the results) and what is NOT working so far..this will make coming up with a solution MUCH easier..

try this:
on column H, copy down if needed.

Please Login or Register  to view this content.

Back.

tgsekhar's solve the problem. It would seem VLOOKUP was what I wanted, as opposed to regular LOOKUP.

And as I said, in the future, I will include examples as an attachment.

tgsekhar, I really appreciate you doing this, especially since you had to record information from an image to get it to work. Do not think that I take that extra manual labour for granted.

SDCh, your solution was also very useful once I dissected it and got it to work. Inf act, it may be even more solid than VLOOKUP, but I understand VLOOKUP better.

I am going to sit and not think for a while... my brain is tired from sifting and filtering through the overwhelming amount of information on both functions...

In this age of information less is more.