more than one lookup value
 

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?

more than one lookup value
 

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?

more than one lookup value
 

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?

more than one lookup value
 

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?

more than one lookup value
 

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?

more than one lookup value
 

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?