Microsoft Microsoft office excel 2007 ac...

I have a sheet in excel which I input my daily results. Some dates or blocks of dates are missing as I am unable to gather the data. (vacation, travel, etc)

Column A is the date (mm/dd/yyyy)

Column B is the result (0.00)I have another sheet with the following:

Column A has a list of all dates (month and day only) (366 rows) Columns B through M have the year in row 1I need to lookup the month and the day and attach the year based on the column year (this is easy)

=VLOOKUP(DATEVALUE(MONTH($A2)&"/"&DAY($A2)&"/"&YEAR(C$1)),Generation!$B:$C,2)BUT I need the exact date data returned. VLOOKUP returns the closest lower value.

If no exact date then return 0 or "".Is there an easy way to do this in Excel without writing code?

Thanks for helping in advance!

If you want an exact match, you need to use FALSE or 0 in your VLOOKUP as the optional range_lookupargument:=VLOOKUP(DATEVALUE(MONTH($A2)&"/"&DAY($A2)&"/"&YEAR(C$1)),Generation!$B:$C,2,

0)Review the VLOOKUP Help file to see the options for this argument.

If an exact match isn't found, this will return #N/A, so wrap the formula in an IF(ISNA()) function to get a "" of 0 if the VLOOKUP evaluates to #N/A. This will return "":

=IF(ISNA(VLOOKUP(DATEVALUE(MONTH($A2)&"/"&DAY($A2)&"/"&YEAR(C$1)),Generation!$B:$C,2,0)),"",VLOOKUP(DATEVALUE(MONTH($A2)&"/"&DAY($A2)&"/"&YEAR(C$1)),Generation!$B:$C,2,0))

Derbydad03 This solved my problem!! - Never used the False argument with VLOOKUP - therefore wasn't looking in this direction.

To solve my Leap year (Feb 29) issue, I used ISERROR vs ISNA.

I also added further to this statement to solve where the date had other data but no daily input (blank cell) This kept the formula from returning a 0 when the date was there. It's a little long but returns the correct data.

=IF(ISBLANK(VLOOKUP(DATEVALUE(MONTH($A7)&"/"&DAY($A7)&"/"&YEAR(B$1)),Generation!$B:$C,2,FALSE)),"",IF(ISERROR(VLOOKUP(DATEVALUE(MONTH($A7)&"/"&DAY($A7)&"/"&YEAR(B$1)),Generation!$B:$C,2,FALSE)),"",VLOOKUP(DATEVALUE(MONTH($A7)&"/"&DAY($A7)&"/"&YEAR(B$1)),Generation!$B:$C,2,FALSE)))

Thanks again!!

Hi, I don't know if this is of any use to you, but to find if the year is a leap year use:

=DAY(EOMONTH(DATE(YEAR(TODAY()),"2","1"),0))For leap years it will return 29 and 28 for other years

If EOMONTH() does not work check to see that you have the Analysis Toolpak Add-In loaded.

From the Office Button, click Excel Options (at bottom)

Select the AddIns Tab and at the bottom make sure the Manage Add-Ins drop-down shows Add-Ins and click Go.

In the dialog box make sure that the box is checked alongside Analysis Toolpak(As I don't have Excel 2007 on this PC, those instructions are from memory, so the names on Tabs etc. may not be absolutely correct).

Regards

Ask Your Question

Weekly Poll