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

*Article last updated on December 07, 2010*

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

### Array formula in B38:

copied down as far as necessary.

### Array formula in C38:

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

Formula for matching a date within a date range

Table of contents Match a date when a date range is entered in a single cell Match a date when […]

The image above demonstrates an array formula in cell E4 that searches for the closest date in column A to the […]

Highlight overlapping date ranges using conditional formatting

The image above demonstrates a conditional formatting formula that colors a record if there is at least one record that […]

Identify overlapping date ranges

The formula in cell F6 returns TRUE if the date range on the same row overlaps another date range in […]

Highlight records based on overlapping date ranges and a condition

adam asks: Hi, I have a situation where I want to count if this value is duplicate and if it […]

Count records between two dates with multiple parameters

Sam asks in this post: Count records between two dates in excel Any chance this would work with multiple parameters. […]

Plot date ranges in a calendar

The image above demonstrates cells highlighted using a conditional formatting formula based on a table containing date ranges. The calendar […]

How to sort cells filtered by two dates

Question: I have a list containg dates and values. How do I sort values between two specific dates? Answer: Yellow […]

Lookup two index columns in excel

Formula in B14: =INDEX(D3:D6, SUMPRODUCT(--(C10=B3:B6), --(C11=C3:C6), ROW(D3:D6)-MIN(ROW(D3:D6))+1)) Alternative array formula #1 in B15: =INDEX(D3:D6, MATCH(C10&"-"&C11, B3:B6&"-"&C3:C6, 0)) Alternative array formula […]

Use MEDIAN function to calculate overlapping ranges

I found an old post that I think is interesting to write about today. Think of two overlapping ranges, it may be dates, […]

### Leave a Reply

### How to comment

**How to add a formula to your comment**

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

**How to add VBA code to your comment**

[vb 1="vbnet" language=","]

Put your VBA code here.

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org or imgur

Use the img tag, like this: <img src="Insert pic link here">

**Contact Oscar**

You can contact me through this contact form