Lookup Limitation?

[jordantk]

I am getting some unexpected behavior from lookups and was hoping someone could clear it up for me. I am working with 2 worksheets. The first has:

SHEET1
B C
1 BBFRSH10 A
2 BBFRSH05 B
3 BBFRSH01 C
4 BBFRZN10 D
5 BBFRZN05 E
6 BBFRSH01B F

SHEET2
This sheet references sheet1, column B to get text from column C using a local reference:

B C
1 BBFRSH05 =LOOKUP(B1,SHEET1!B1:B5,SHEET1!C1:C5)
2 BBFRZN10 =LOOKUP(B2,SHEET1!B1:B5,SHEET1!C1:C5)
3 BBFRSH01 =LOOKUP(B3,SHEET1!B1:B5,SHEET1!C1:C5)

I will get the following for values in column C:
C1 = A
C2 = F
C3 = A

I should be getting:
C1 = B
C2 = D
C3 = C

I have tried modifying the product numbers to make the references work correctly and if I change them to something like:

BBFRSH1
BBFRSH2
BBFRSH3
BBFRSH4
...

it will work, but if I change the first 6 letters of the value, I will get incorrect references. I have also tried changing the format of the cell and nothing has worked so far. Why don't my values work? This seems like its too basic a problem to not be known, is this some type of cludgy limitation of Excel?

Thanks.

[Dean England]

jordantk,

Your lookup list in column B does not appear to be in order. I think if you sort your lookup data with column B in ascending order you may get the results you want. The other alternative might be index/match.

Dean

[jordantk]

Thanks, that was it. A good thing to know, although I'm kinda disappointed that the values need to be in ascending order for it to even work correctly. Thats Microsoft for you.

[Dean England]

jordantk,

Another idea for you. Use Vlookup with "False" which gives an exact match without sorting.

=VLOOKUP(B1,Sheet1!B1:C6,2,FALSE)

A small attachment below.

Dean