Any ideas why a reference that works will in 2003 has problems in

[george]

I am using a reference shown here =LOOKUP("X",Y11:Y15,X11:X15) to find a
number in a column to the right of an “Xâ€


0
X 1
2
3
4

In this example the x is next to the number 1, and should return that value-
however in 2007 excel it seems to show the value 4 when the x is next to the
2 ( or at least some other number).

Thoughts ideas?


george

 

[T. Valko]

Works the same in both versions for me. Are you sure the "x" is the only
entry in the range? Might there be formula blanks?

Use this instead:

=INDEX(X11:X15,MATCH("x",Y11:Y15,0))

 

[Ron Rosenfeld]

I am using a reference shown here =LOOKUP("X",Y11:Y15,X11:X15) to find a
number in a column to the right of an “X”


0
X 1
2
3
4

In this example the x is next to the number 1, and should return that value-
however in 2007 excel it seems to show the value 4 when the x is next to the
2 ( or at least some other number).

Thoughts ideas?


george

Your formula seems to work fine here. Perhaps some issue with your data?
--ron

 

[george]

Hey thanks guys-
Ill give the index a try…. Out of curiosity why does the index work better
than a “lookup†?

Cheers
george

 

[T. Valko]

The LOOKUP function works just fine, but under strict constraints. The
lookup_vector *must* be sorted in ascending order otherwise you'll get
incorrect results.

The combination of INDEX/MATCH gives more leeway in that the lookup_array
can be either sorted or unsorted.