Extract dates and adjacent value in a range using a date critera

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