find oldest date with a condition

[3Suk]

dear all,
please advise a formula for finding the oldest date in below two columns :

A1=10, B1=22/5/2013
A2=10, B2=15/5/2013
A3=10, B3=27/5/2013
A4=20, B4=14/5/2013
A5=20, B5=29/5/2013

Condition : only choose when column A = 10
Expected result : 15/5/2013


Thanks,
Patrick.

 

[Claus Busch]

Hi,

Am Mon, 19 Aug 2013 02:10:45 -0700 (PDT) schrieb 3Suk:

A1=10, B1=22/5/2013
A2=10, B2=15/5/2013
A3=10, B3=27/5/2013
A4=20, B4=14/5/2013
A5=20, B5=29/5/2013

Condition : only choose when column A = 10
Expected result : 15/5/2013

try:
=MIN(IF(A1:A5=10,B1:B5))
and enter as array formula with CTRL+Shift+Enter


Regards
Claus B.

 

[3Suk]

dear all,

please advise a formula for finding the oldest date in below two columns :



A1=10, B1=22/5/2013

A2=10, B2=15/5/2013

A3=10, B3=27/5/2013

A4=20, B4=14/5/2013

A5=20, B5=29/5/2013



Condition : only choose when column A = 10

Expected result : 15/5/2013





Thanks,

Patrick.

Claus,
thanks for your input. It seems work, but when I update in my worksheet, only the first row has worked fine, but the other row returned a zero. I'm testing it, not sure it relates to the table format with array formula or not.

 

[Claus Busch]

Hi,

Am Tue, 20 Aug 2013 03:20:29 -0700 (PDT) schrieb 3Suk:

thanks for your input. It seems work, but when I update in my worksheet, only the first row has worked fine, but the other row returned a zero. I'm testing it, not sure it relates to the table format with array formula or not.

you may not have blank cells in the matrix. Then you get a zero.
Or try:
=MIN(IF(A1:A6=10,IF(B1:B6,B1:B6)))
and enter with CTRL+Shift+Enter


Regards
Claus B.

 

[3Suk]

Hi,



Am Tue, 20 Aug 2013 03:20:29 -0700 (PDT) schrieb 3Suk:






you may not have blank cells in the matrix. Then you get a zero.

Or try:

=MIN(IF(A1:A6=10,IF(B1:B6,B1:B6)))

and enter with CTRL+Shift+Enter





Regards

Claus B.

--

Win XP PRof SP2 / Vista Ultimate SP2

Office 2003 SP2 /2007 Ultimate SP2

Claus,
sorry for the late reply and thanks for your help. My problem solved.