using a date in vlookup

  • Thread starter Thread starter Tonso
  • Start date Start date
T

Tonso

i want to perform a vlookup using the Now() function to generate the
lookup value (A1), the 1st column in the table [col B] array will be
all the dates in a year listed consequtive,, and the 2nd column being
a value assigned to each day in the 1st column . My formula is
vlookup(A1,B1:C367,2). The result I get is "#N/A. What am i doing
wrong?

Thanks

Tonso
 
NOW() returns both the date and the time, so you would be better off
using TODAY(), which only returns the date.

Another problem might be that your dates in column B are really text
values that just look like dates - see what happens if you set the
format in column B to General. You should see the dates change to
numbers (of around 39000 if they are fairly recent dates), but if they
are text values they will remain unchanged.

Hope this helps.

Pete
 
I would guess that your data types are not identical.

Since you're using the Now() function to return the lookup value, that value
in A1 is an actual XL recognizable date.

The data in Column B is probably a text entry, or an imported entry, not
perceived by XL to be a date.
Even if the dates in Column B appear to be "legal", an imbedded <space>
anywhere in Column B's data is enough to generate a mismatch, and return the
#N/A error.

Format A1 and a cell in Column B to Date, and manually key in an identical
value, and see then if your formula returns the expected result.
 
I would guess that your data types are not identical.

Since you're using the Now() function to return the lookup value, that value
in A1 is an actual XL recognizable date.

The data in Column B is probably a text entry, or an imported entry, not
perceived by XL to be a date.
Even if the dates in Column B appear to be "legal", an imbedded <space>
anywhere in Column B's data is enough to generate a mismatch, and return the
#N/A error.

Format A1 and a cell in Column B to Date, and manually key in an identical
value, and see then if your formula returns the expected result.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------




i want to perform a vlookup using the Now() function to generate the
lookup value (A1), the 1st column in the table [col B] array will be
all the dates in a year listed consequtive,, and the 2nd column being
a value assigned to each day in the 1st column . My formula is
vlookup(A1,B1:C367,2). The result I get is "#N/A. What am i doing
wrong?
Thanks
Click to expand...

Tonso- Hide quoted text -
Click to expand...


- Show quoted text -
Click to expand...


I had already formatted all as date. However, when I changed the
function to TODAY...all was well! Thanks very much to all!!
Tonso
 
Back
Top