LOOKUP function different?

  • Thread starter Thread starter MLT
  • Start date Start date
M

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?
 
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?
Click to expand...

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))
 
Back
Top