vlookup using date

[Guest]

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!

 

[Gord Dibben]

Please post the formula you are using.


Gord Dibben MS Excel MVP

 

[Guest]

=VLOOKUP(E9,A1:B3,2,FALSE)
worked for me,you must ensure your lookup values are the same as the values
in your table,and you have the tables sorted correctly if you arent using
false as the 4th argument.Check that your lookup values are the same by
comaring the values directly,ie in a cell type a date that ilooks the same as
one in your table ie =a1=b1.If the result is false even tho the dates look
the same thats where your problem is

 

[Guest]

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!

 

[daddylonglegs]

In this formula

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

1/1/2001 is not interpreted as a date but as 1 divided by 1 divided by
2001. TRy either

=VLOOKUP(DATE(2001,1,1),A1:C4,3)

or

=VLOOKUP("1/1/2001"+0,A1:C4,3)

 

[Gord Dibben]

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


Gord