Vlookup w/Date Function

I use VLOOKUP with DATE function as the lookup value. I set the
range_lookup=FALSE so I am expecting an exact match or an error will result
(#NA).
Within the DATE function, I determine the year and month and then I supply a
day value between 0-31. Everything works as expected if the day value is
between 1-31. But if the day value is 0, then the VLOOKUP returns result for
the last day of the previous month instead of an error. Here is the function:
VLOOKUP(DATE(YEAR(R31C2),MONTH(R31C2),DAY(R[-1]C)),
Scheduled_Installation_Date,2,FALSE)

Force an error by using this in place of your DAY() function:

IF(DAY(R[-1]C)=0,NA() ,DAY(R[-1]C))

"cym" wrote:

> I use VLOOKUP with DATE function as the lookup value. I set the
> range_lookup=FALSE so I am expecting an exact match or an error will result
> (#NA).
> Within the DATE function, I determine the year and month and then I supply a
> day value between 0-31. Everything works as expected if the day value is
> between 1-31. But if the day value is 0, then the VLOOKUP returns result for
> the last day of the previous month instead of an error. Here is the function:
> VLOOKUP(DATE(YEAR(R31C2),MONTH(R31C2),DAY(R[-1]C)),
> Scheduled_Installation_Date,2,FALSE)
>