Question: I have a range of dates and values. See picture below. I would like to identify the largest value where the adjacent date to the left is the year 2009 and the month is November?

Largest value from a range using date criteria

Answer: I have colored the dates and values that meet the date criteria.

The array formula works only if the first and every other column in the range is a date column. In this example column B, D and F and so on.

Try changing year (C15) and month (C16) and see how the result in C19 changes.

Use the array formula in this post:  Extract cell values in a range using criteria in excel to identify the adjacent date.

To get the smallest value, change MAX to MIN in array formula below.

Array formula in C19:

=MAX(IF((MOD(COLUMN($B$3:$F$12)-MIN(COLUMN($B$3:$F$12))+1, 2)=1)*(YEAR($B$3:$F$12)=$C$15)*(MONTH($B$3:$F$12)=$C$16), $C$3:$G$12, "")) + CTRL + SHIFT + ENTER

Download excel example file for this tutorial.

Lookup min max values within a date range in a range.xls
(Excel 97-2003 Workbook *.xls)

Functions in this article:

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

MOD(number, divisor) returns the remainder after a number is divided by a divisor

COLUMN(reference) returns the column number of a reference

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

MAX(number1,[number2],) Returns the largest value in a set of values. Ignores logical values and text

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

Related articles:

Related posts:

Count unique distinct values using date criteria in a range in excel

Lookup two index columns using min max values and a date range as criteria

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

Create a quartely date range in excel

How to calculate missing months in a given date range in excel