Lookup function gives wrong values occasionally

[becona]

I wanted to use the lookup function to get a numerical value with
reference to my lookup value, which is a text entry. For the setup of
my spread sheet, I could not use vlookup or hlookup. This lookup
function gave me some correct values, but mostly incorrect ones. The
following is my command:

=LOOKUP(DL2,$AV2:$BL2,$BM2:$CC2)

I checked the lookup value and the lookup vector... they are exactly
the same without spacing or spelling errors. Since I got some correct
values and some incorrect ones, I suspected that the lookup function
does not work for text entry. Am I right? Is there any way that I can
fix the problem? Please advice.

Thanks.

 

[Guest]

LOOKUP requires the values to be is ascending sequence otherwise you will not
(always) get the correct results. Is your data sorted in ascending sequence?

 

[Biff]

Hi!

Try this:

=INDEX(BM2:CC2,MATCH(DL2,AV2:BL2,0))

Biff

 

[becona]

Thanks, I don't know that the values have to be ascending order. My
data is not in ascending order and it is almost impossible to set it up
this way (otherwise I don't have to do the lookup thing!) Anyway, good
to know more about the lookup function and thanks again.