MIN within range based on criteria

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have two rows of dates, row A - "Start" date & row B - "Due" date. In cell
A1 user can select a start date from the range in row A below. There will be
multiple lines that have same "Start" date but the "Due" date could vary. I
would like the MIN "Due" date value to appear in B1 based on the
cooresponding "Start" date user chooses in cell A1.
ie.
A1. user chooses start date / B1. MIN value row B appears
A2. 12/1/5 B2. 12/4/5
A3. 12/2/5 B3. 12/6/5
A4. 12/3/5 B4. 12/6/5
A5. 12/3/5 B5. 12/5/5
if user chooses start date of 12/3/5 the MIN value within row B range is
12/5/5. Can someone help me write a formula for B1.
Thanks, Steven
 
One way .

Put in the formula bar for B1 and array-enter
(i.e. press CTRL+SHIFT+ENTER):
=IF(A1="","",MIN(IF(A2:A100=A1,B2:B100)))
 
If you would sort your data using column A as the primary key ascending and
column B as the secondary dey ascending, then the following in B1 will
do........(ranges based on your sample data, change as required)

=IF(AND(ISNUMBER(A1),A1>=MIN(A2:A5)),VLOOKUP(A1,A2:B5,2,FALSE),"No
acceptable date in A1")

Vaya con Dios,
Chuck, CABGx3
 
Thanks Chuck, but relying on others to fill in the data and can expect no
consistency. The formula will be for multiple others and not always there to
explain below. I need to stay away from index and Vlookup formulas.

Brgds and thanks for the quick response.
Steven
 
Max, I left one thing out... there are two cells for the user to set
criteria. A "Start" and "End" date.
A2:A100>=A1
and
A2:A100<=B1

Don't suppose you have a solution? (thought I'd be able to sort this out
myself)
Rgds, Steven
 
With the start and end dates in A1 and B1,

Try this revised formula in C1 (array-entered as before):
=IF(OR(A1="",B1=""),"",MIN(IF((A2:A100>=A1)*(A2:A100<=B1),B2:B100)))

Adapt the ranges to suit ..
 
Back
Top