Author: Oscar Cronquist Article last updated on December 06, 2010

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?

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

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

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: