LOOKUP function different?

[MLT]

I must be missing something. I have a matrix like this.

In cells A1, B1, C1 are the words dog, cat, bear.
In cells A2, B2, C2 are numbers 3, 4, 5

In cell A4 is the formula "=LOOKUP("dog",A1:C1,A2:C2)", which returns
5.
If the formula is "=LOOKUP("cat",A1:C1,A2:C2)", it returns 4
If the formula is "=LOOKUP("bear",A1:C1,A2:C2)", it returns #NA
If the formula is "=LOOKUP("duck",A1:C1,A2:C2)", it returns 5

Whats going on here?

 

[Jim Cone]

The values in the lookup_vector must be placed in ascending order.
Looks like you need "bear, cat, dog" in those 3 cells.
--
Jim Cone
Portland, Oregon USA
http://excelusergroup.org/




"MLT" <[email protected]>
wrote in message
news:54954558-8034-4011-a585-66e84cb06995@n10g2000yqd.googlegroups.com...

 

[joeu2004]

In cells A1, B1, C1 are the words dog, cat, bear.
In cells A2, B2, C2 are numbers 3, 4, 5

In cell A4 is the formula "=LOOKUP("dog",A1:C1,A2:C2)",
which returns 5.
If the formula is "=LOOKUP("cat",A1:C1,A2:C2)", it returns 4
If the formula is "=LOOKUP("bear",A1:C1,A2:C2)", it returns #NA
If the formula is "=LOOKUP("duck",A1:C1,A2:C2)", it returns 5
Whats going on here?

LOOKUP requires that the lookup table, A1:C1, is in ascending order.
If it is not, the results are unpredictable due to the implementation
of a binary search.

It would be ideal if you can sort A1:C2 is ascending order according
to row 1. The lookup would be most efficient that way; it can make a
big difference if the actual matrix is much larger than your examples.

Alternatively, use =HLOOKUP("cat",A1:C2,2,FALSE).

Or if the lookup and result vectors are not actually contiguous or if
the lookup vector is not above the result vector as they are in your
example, use:
=INDEX(A2:C2,MATCH("cat",A1:C1,0))