The formulas below extract all dates where the year is 2009 and adjacent values in range B2:G33.

Array formula in B38:

=INDEX(tbl, SMALL(IF(IF(ISERROR(YEAR(tbl)=2009), 0, YEAR(tbl)=2009), ROW(tbl)-MIN(ROW(tbl))+1), ROWS(B14:$B$14)), MATCH(SMALL(IF(IF(ISERROR(YEAR(tbl)=2009), 0, YEAR(tbl)=2009), (ROW(tbl)-MIN(ROW(tbl))+1)+(COLUMN(tbl)-MIN(COLUMN(tbl))+1)/16384, ""), ROWS(B14:$B$14)), SMALL(IF(IF(ISERROR(YEAR(tbl)=2009), 0, YEAR(tbl)=2009), ROW(tbl)-MIN(ROW(tbl))+1), ROWS(B14:$B$14))+((COLUMN(tbl)-MIN(COLUMN(tbl))+1)/16384), 0)) + CTRL + SHIFT + ENTER copied down as far as necessary.

Array formula in C38:

=INDEX(tbl, SMALL(IF(IF(ISERROR(YEAR(tbl)=2009), 0, YEAR(tbl)=2009), ROW(tbl)-MIN(ROW(tbl))+1), ROWS($B14:C$14)), MATCH(SMALL(IF(IF(ISERROR(YEAR(tbl)=2009), 0, YEAR(tbl)=2009), (ROW(tbl)-MIN(ROW(tbl))+1)+(COLUMN(tbl)-MIN(COLUMN(tbl))+1)/16384, ""), ROWS($B14:C$14))+1/16384, SMALL(IF(IF(ISERROR(YEAR(tbl)=2009), 0, YEAR(tbl)=2009), ROW(tbl)-MIN(ROW(tbl))+1), ROWS($B14:C$14))+((COLUMN(tbl)-MIN(COLUMN(tbl))+1)/16384), 0)) + CTRL + SHIFT + ENTER copied down as far as necessary.

Named ranges
tbl (B2:G33)
What is named ranges?

How to customize the formula to your excel spreadsheet
Change the named range and the year in the above formula.

Download excel example file.
Extract dates and adjacent value in a range using a date criteria.xls
(Excel 97-2003 Workbook *.xls)

Functions in this article:

COUNTIF(range,criteria)
Counts the number of cells within a range that meet the given condition

IF(logical_test;[value_if:true];[value_if_false])
Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

INDEX(array,row_num,[column_num])
Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

ROW(reference) returns the rownumber of a reference

SMALL(array,k) returns the k-th smallest row number in this data set.

MATCH(lookup_value;lookup_array; [match_type])
Returns the relative position of an item in an array that matches a specified value

MIN(number1,[number2])
Returns the smallest number in a set of values. Ignores logical values and text

ISERROR(value)
Checks whether a value is an error and returns TRUE or FALSE

YEAR(serial_number) returns the year of a date, an integer of the range 1900-9999

COLUMN(reference) Returns the column number of a reference

ROWS(array) returns the number of rows in a reference or an array

Related posts:

1. Extract numbers and text from a range using array formula in excel
2. Sort dates within a date range using excel array formula
3. Most common value between two dates in a range in excel
4. Extract cell values in a range using a criterion in excel
5. Create a unique distinct list from a date range in excel
6. Finding the nearest date in a range of dates using excel formula
7. Extract distinct unique sorted year and month list from a date series in excel
8. Extract all rows from a range that meet criteria in one column in excel
9. How to calculate missing months in a given date range in excel
10. Count date records between two dates in a range in excel