Excel 2000: LOOKUP returning wrong values 1

[goopit]

Hello,

This is my little formula, =LOOKUP(A2,Sheet1!$A$2:$B$365).

In most instances, it returns the correct value for what is input into A2. However, in some cases, it will return an incorrect value. I've double-checked Sheet 1 to be sure the correct value is in place, but for some numbers, it doesn't work.

Any assistance will be greatly appreciated.

Thank You,

goopit

 

[jfhewitt]

The formula looks fishy to me. I would think you have problems when your lookup value is the value in the first cell of your lookup table.

 

[Corgano]

Goopit,

What are you looking to get out of this formula? What data do you have in your table and what are you trying to search for/what result are you looking for?

I've never really found a use for the "LOOKUP" function, there's usually another easier and more efficient way.

 

[goopit]

The lookup value is not the same cell value used in the formula - it is on a different sheet.

Anyway, on Sheet 1, there is a list of product numbers in one column and their associated product names in the adjacent column. I want someone to able to enter a product number and have the correct product name automatically appear in the column next to it and it works fine, except for a few instances.

goopit

 

[ABerkowicz]

Here are some troubleshooting tips...

Make sure that you do not have any duplicates in what you are looking up. You will get bad results on lookup functions when there are multiple records for the same thing.

Another thing is that you should end with ,false (or some other handler) in your expression to handle those items not in the list - for instance:

=LOOKUP(A6,Sheet1!$A$2:$B$365,FALSE)

What exactly is the error or problems that are happening in those few cases?

 

[Corgano]

That's what I thought you might be trying to do...

=VLOOKUP(A2,Sheet1!$A$2:$B$365,2,FALSE)

Will do the trick for you. The VLOOKUP function allows you to specify the "Col_Index" (in this case 2 because you want the values from column B) and "FALSE" ensure the function will return only exact matches.

Of course there should be no duplicate product numbers as this will cause problem with any formula you use!

 

[goopit]

Corgano,

Hot damn! That did it. Million thanks to you.

Sincerely,
goopit