=VLOOKUP(DATEVALUE("1/1/2001"),A1:C4,3,FALSE)


Gord

On Sat, 29 Jul 2006 15:45:01 -0700, little bear
wrote:

the formula is am using is:
=VLOOKUP(1/1/2001,A1:C4,3)

when i use the cell reference A1 instead of 1/1/2001 in the formula, it
works, which is what led me to the idea of using the datevalue. It isn't
possible to easily find a cell with the right date in it, though, as it might
be thousands of rows down. thank you!

"Gord Dibben" wrote:

Please post the formula you are using.


Gord Dibben MS Excel MVP

On Sat, 29 Jul 2006 14:56:01 -0700, little bear
wrote:

I have data that looks like:
date value1 value2
1/1/01 200 222
1/2/01 340 444
1/3/01 450 532

and i want to look up value1 and value2 based on dates. when i use vlookup
it always produces #NA. i have tried different formatting and it does work
when i convert the date to datevalue and base the lookup on the corresponding
datevalue. why is this? is date not an allowed lookup value for the vlookup
function?

i have lots of data sets with date in different formats that are
periodically added to, and need to find some system for looking up values
without going thru a cumbersome process of finding the datevalues for the
dates i am looking for then inserting that value in the vlookup formula
rather than the actual date.

thank you!