MIN within range based on criteria

[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

 

[Max]

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

 

[CLR]

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

 

[Guest]

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

 

[Guest]

Thanks Max, worked like a charm.
Best Regards,
Steven

 

[Max]

Glad to hear that, Steven !
Thanks for the feedback ..

 

[Guest]

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

 

[Max]

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

 

[Guest]

Again, worked liked a charm. Thanks for your help MAX!

Best Regards

 

[Max]

You're welcome !
Thanks for posting back ..