## Largest value in a range using date criteria in excel

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

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

Highlight overlapping date ranges using conditional formatting

How to highlight overlapping date ranges Click "Home" tab Click "Conditional Formatting" button Click "New Rule.." Click "Use a formula […]### 4 Responses to “Largest value in a range using date criteria in excel”

### Leave a Reply

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

<code>your formula</code>

Remember to convert less than and larger than signs to html character entities before you post your comment.

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

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

VBA code

[/vb]

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

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

Here is a shorter formula which I believe returns the maximum value being sought after (it cannot be easily modified for the minimum value though)...

=MAX((TEXT(B3:F12*(MOD(COLUMN(B3:F12),2)=0),"myyyy")=C16&C15)*C3:G12)

I should have mentioned that my formula, like yours, is an array formula requiring Ctrl+Shift+Enter to commit it, not just Enter by itself.

Thanks!!

Using your formula, I created this formula returning minimum value:

=MIN(IF((TEXT(B3:F12*(MOD(COLUMN(B3:F12),2)=0),"myyyy")=C16&C15),C3:G12,"")) + CTRL + SHIFT + ENTER

I guess my formula was easier to modify than it seemed it would be in my "mind's eye" when I originally considered it... good job Oscar!