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)