vlookup(date, range,3)

[David]

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

 

[T. Valko]

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?

 

[David]

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

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





.

 

[T. Valko]

Good deal. Thanks for the feedback!