## Lookup min max values within a date range

This article explains how to find the smallest and largest 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:

The MAXIFS function returns the largest number from *max_range *($C$10:$C$39) based on a condition or criteria.

MAXIFS(*max_range*, *criteria_range1*, *criteria1*, [*criteria_range2*, *criteria2*], ...)

The first condition $B$10:$B$39, "<="&$E$3 identifies rows whose date is smaller than or equal to date in cell E3.

The second condition $B$10:$B$39, ">="&$C$3 identifies rows whose date is larger than or equal to date in cell C3.

Formula in cell C7:

The MINIFS function works exactly the same as the MAXIFS function, however, the smallest number is instead returned.

Formula in cell E6:

This formula is a simple lookup formula, it returns only a single value from column B if the value in column C matches the contents of cell C6.

INDEX($B$10:$B$39,MATCH(C6,$C$10:$C$39,0))

The MATCH function returns the relative position of a value in a cell range or array.

INDEX($B$10:$B$39,MATCH(1030.98,$C$10:$C$39,0))

becomes

INDEX($B$10:$B$39,5)

The INDEX function returns a value based on a row (and column number if needed).

and returns 8-27-09 in cell E6.

Formula in cell E7:

### Download excel *.xlsx file

Question: I am trying to create an excel spreadsheet that has a date range. Example: Cell A1 1/4/2009-1/10/2009 Cell B1 [โฆ]

Find latest date based on a condition

Table of contents Lookup a value and find max date How to enter an array formula Explaining array formula Download [โฆ]

Formula for matching a date within a date range

Table of contents Match a date when a date range is entered in a single cell Match a date when [โฆ]

How to use the MAXIFS function

The MAXIFS function allows you to calculate the highest value based on a condition or criteria. Formula in cell G4: [โฆ]

SMALL function with duplicates

The formulas in column E, shown in the picture above, extracts the k-th smallest value from B3:B9 ignoring the duplicate numbers. [โฆ]

Find the smallest value in a list that is larger than a number

Problem: Find the smallest value in a list but it has to be bigger than 45? Answer: MINIFS function [Excel [โฆ]

### 7 Responses to โLookup min max values within a date rangeโ

### Leave a Reply

### How to comment

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

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

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

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

Put your VBA code here.

[/vb]

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

Upload picture to postimage.org or imgur

Paste image link to your comment.

**Contact Oscar**

You can contact me through this contact form

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!