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:
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