vlookup(date, range,3)

Hello Group,

I am doing a vlookup on a date, I will take the closest date, so have not
used the last parameter. It returns the last date in the list always. Both
the date in the Lookup and in the table are type = 1, a number.

Am I missing something here or is it not possible to do this?

Thanks,
David

I will take the closest date, so have not used the last parameter.
vlookup(date, range,3)
It returns the last date in the list always.

When you omit the range_lookup argument the table_array *must* be sorted by
the date in ascending order. The "closest" date will be the closest date
that is less than or equal to the lookup date. For example:

1/1/2010...10
1/5/2010...25
1/8/2010...42

If your lookup date was 1/7/2010 the formula would "match" 1/5/2010 and the
result would be 25.

1/5/2010 is the closest date that is *less than or equal to* the lookup date
1/7/2010.

Is that what you had in mind?

--
Biff
Microsoft Excel MVP


"David" wrote in message
...
Hello Group,

I am doing a vlookup on a date, I will take the closest date, so have not
used the last parameter. It returns the last date in the list always. Both
the date in the Lookup and in the table are type = 1, a number.

Am I missing something here or is it not possible to do this?

Thanks,
David

Thanks. I changed it to "false" and it worked.

"T. Valko" wrote:

I will take the closest date, so have not used the last parameter.
vlookup(date, range,3)
It returns the last date in the list always.

When you omit the range_lookup argument the table_array *must* be sorted by
the date in ascending order. The "closest" date will be the closest date
that is less than or equal to the lookup date. For example:

1/1/2010...10
1/5/2010...25
1/8/2010...42

If your lookup date was 1/7/2010 the formula would "match" 1/5/2010 and the
result would be 25.

1/5/2010 is the closest date that is *less than or equal to* the lookup date
1/7/2010.

Is that what you had in mind?

--
Biff
Microsoft Excel MVP


"David" wrote in message
...
Hello Group,

I am doing a vlookup on a date, I will take the closest date, so have not
used the last parameter. It returns the last date in the list always. Both
the date in the Lookup and in the table are type = 1, a number.

Am I missing something here or is it not possible to do this?

Thanks,
David


.

Good deal. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"David" wrote in message
...
Thanks. I changed it to "false" and it worked.

"T. Valko" wrote:

I will take the closest date, so have not used the last parameter.
vlookup(date, range,3)
It returns the last date in the list always.

When you omit the range_lookup argument the table_array *must* be sorted
by
the date in ascending order. The "closest" date will be the closest date
that is less than or equal to the lookup date. For example:

1/1/2010...10
1/5/2010...25
1/8/2010...42

If your lookup date was 1/7/2010 the formula would "match" 1/5/2010 and
the
result would be 25.

1/5/2010 is the closest date that is *less than or equal to* the lookup
date
1/7/2010.

Is that what you had in mind?

--
Biff
Microsoft Excel MVP


"David" wrote in message
...
Hello Group,

I am doing a vlookup on a date, I will take the closest date, so have
not
used the last parameter. It returns the last date in the list always.
Both
the date in the Lookup and in the table are type = 1, a number.

Am I missing something here or is it not possible to do this?

Thanks,
David


.