this is trivial example
1-May 1
1-May 2
1-May 3
2-May 4
3-May 5

the formula
=LOOKUP(A1,A1:A10,B1:B10)
gives 3 and not 1 or 2. why?

What result do you want??
--
Thanks,
MarkN


"R..VENKATARAMAN" wrote:

this is trivial example
1-May 1
1-May 2
1-May 3
2-May 4
3-May 5

the formula
=LOOKUP(A1,A1:A10,B1:B10)
gives 3 and not 1 or 2. why?

Hi!

My "guess" is that because the lookup_vector is sorted ascending (which is
required in order for it to work properly), it "finds" the first instance
that is less than or equal to the lookup_value compared to the next value
that is either greater than the lookup_value or the last value in the
lookup_vector

Vlookup works the same way when the range_lookup argument is set to 1 or
TRUE or omitted:

=VLOOKUP(A1,A1:B10,2)

Returns 3

Biff

"R..VENKATARAMAN" wrote in message
...
this is trivial example
1-May 1
1-May 2
1-May 3
2-May 4
3-May 5

the formula
=LOOKUP(A1,A1:A10,B1:B10)
gives 3 and not 1 or 2. why?

Lookup returns the largest value that is less than or equal to your criteria.
In this case, the third 1 because the value after it is 2.

If you want the first occurance, consider VLookup.

"R..VENKATARAMAN" wrote:

this is trivial example
1-May 1
1-May 2
1-May 3
2-May 4
3-May 5

the formula
=LOOKUP(A1,A1:A10,B1:B10)
gives 3 and not 1 or 2. why?

thank you.
"JMB" wrote in message
...
Lookup returns the largest value that is less than or equal to your
criteria.
In this case, the third 1 because the value after it is 2.

If you want the first occurance, consider VLookup.

"R..VENKATARAMAN" wrote:

this is trivial example
1-May 1
1-May 2
1-May 3
2-May 4
3-May 5

the formula
=LOOKUP(A1,A1:A10,B1:B10)
gives 3 and not 1 or 2. why?

You are welcome.

"R..VENKATARAMAN" wrote:

thank you.
"JMB" wrote in message
...
Lookup returns the largest value that is less than or equal to your
criteria.
In this case, the third 1 because the value after it is 2.

If you want the first occurance, consider VLookup.

"R..VENKATARAMAN" wrote:

this is trivial example
1-May 1
1-May 2
1-May 3
2-May 4
3-May 5

the formula
=LOOKUP(A1,A1:A10,B1:B10)
gives 3 and not 1 or 2. why?