Earlest Date Based On A Date Using Min Formula

[Cue]

I'm triyng to get the earlest date under DATE1 (coloumn A) based on a date
under DATE 2 (coloumn B) by using the MIN formula.

DATE 1 DATE 2
4/30/2008 4/30/2008
4/30/2008 4/30/2008
4/29/2008 4/30/2008
4/28/2008 4/29/2008
4/27/2008 4/29/2008

=MIN(IF(B2:B6="4/30/2008",A2:A6))

4/27/2008 is the answer I get. But it should be 4/29/2008. Can someone show
the correct formula for this?

 

[Gary''s Student]

=MIN(IF(B2:B6=DATEVALUE("4/30/2008"),A2:A6))

Must be entered as a Array Formula (enter with CNTRL-SHFT-ENTER rather than
just the ENTER key)

 

[Cue]

It works for the data below. Thanks Gary. But I also have another similar
spreadsheet with about 15k rows of dates. Column A has blanks cells. So when
I used the Array Formula, it gives me a value of 0. Any suggestions?

 

[T. Valko]

Try this...

A1 = your date variable

Array entered:

=MIN(IF((Date2=A1)*(Date1<>""),Date1))

Format as Date

 

[Cue]

It works. Thank you!
--
Cue

Try this...

A1 = your date variable

Array entered:

=MIN(IF((Date2=A1)*(Date1<>""),Date1))

Format as Date

 

[T. Valko]

You're welcome!

--
Biff
Microsoft Excel MVP

It works. Thank you!