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

Do you think Samsung's Bixby will compete well against other phone AI systems?

Discuss in The Lounge

Poll History