## Lookup min max values within a date range

*Article updated on September 22, 2017*

This post demonstrates how to find minimum and maximum value using two conditions. In this case they are date conditions but they can be whatever you like, displayed in cell C3 and E3.

The maximum value in that date range is calculated in cell C6, The minimum value in cell C7. There is also a formula that finds these values and return their corresponding date, in cell E6 and E7.

Formula in cell C6:

Formula in cell C7:

Formula in cell E6:

Formula in cell E7:

### Download excel *.xlsx file

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 […]### 7 Responses to “Lookup min max values within a date range”

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

How would you go about doing the above while excluding any values that happen to be zero?

Thomas

=MIN(IF((Date_col< =$E$3)*(Date_col>=$C$3)*(Close_col<>0), Close_col, ""))

You are a lifesaver! I've been trying to figure this out for hours, until I came across your site.

What does the asterisk do in the formula? I know it's not multiplying anything, but I have never seen it this way.

=MIN(IF((Date_col=$C$3)*(Close_col0), Close_col, ""))

Mark McPherson,

Thank you!

The asterisk multiplies two arrays.

Example, (TRUE, TRUE, FALSE)*(TRUE, FALSE, FALSE) equals (1,0,0).

1 = TRUE

0 = FALSE

If there are repeated values in the close column, the expression returns the first occurrence of the value, regardless of the identified data range. how do we modify the formula to return the value in the date range identified?

its so heplful! thank you so much

Thanks, this one was giving me a headache!