Dates and Excel

Microsoft Microsoft office excel 2007 ac...
March 12, 2010 at 12:19:55
Specs: Windows XP
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 1

I need to lookup the month and the day and attach the year based on the column year (this is easy)

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!

See More: Dates and Excel

Report •

March 12, 2010 at 12:32:23
If you want an exact match, you need to use FALSE or 0 in your VLOOKUP as the optional range_lookup argument:


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 "":


Report •

March 12, 2010 at 13:51:48

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.


Thanks again!!

Report •

March 12, 2010 at 17:48:36

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


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).


Report •

Related Solutions

Ask Question