Formula to look up Min Values in Date with 1 or more criteria
 

Hi Mike,

Sorry to correct my mistake, the formula I used was
={IF(B2="BCD",AL7,MIN(IF($A$2:$A$5=A2,$C$2:$C$5))) }
I had used the array formula over it.

The reason why I was returned the date from BCD was becos' of my criteria
was set to "Name". Is there any way I can check the Name and also limit the
minimum date value to only those under Company "ABC"?

Thanks,
Carine

"Mike H" wrote:

Is there any way I can capture the minimum date for the only for Company
"ABC" and ignore the date for "BCD":

You never read my response. The formula I gave you is an ARRAY formula and
will return excactly what your looking for if you follow the instructions.

Paste the formula in the formula bar then:

This is an array formula which must be entered by pressing CTRL+Shift+Enter
'and not just Enter. If you do it correctly then Excel will put curly brackets
'around the formula {}. You can't type these yourself. If you edit the formula
'you must enter it again with CTRL+Shift+Enter.
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"Carine" wrote:

Hi Mike,

Thanks very much for your help.
Tried the formula out and it works well. However, I noticed that with the
mentioned formula, i have the following end results:

Formula used: =IF(B2="BCD",AL7,MIN(IF($A$2:$A$5=A2,$C$2:$C$5)))

Name Company Date End Result
Alex ABC 31-12-2009 31-12-2006
Alex ABC 31-12-2008 31-12-2006
Alex ABC 30-11-2009 31-12-2006
Alex BCD 31-12-2006 31-12-2006


Is there any way I can capture the minimum date for the only for Company
"ABC" and ignore the date for "BCD":
Name Company Date End Result
Alex ABC 31-12-2009 31-12-2008
Alex ABC 31-12-2008 31-12-2008
Alex ABC 30-11-2009 31-12-2008
Alex BCD 31-12-2006 31-12-2006


Thanks,
Carine


"Mike H" wrote:

Hi,

I have assumed your data are in columns A, B & C.
Put this ARRAY formula in D2 enter as an ARRAY (see below) and drag down

=IF(B2="BCD",C2,MIN(IF($B$2:$B$10=B2,$C$2:$C$10)))

This is an array formula which must be entered by pressing CTRL+Shift+Enter
'and not just Enter. If you do it correctly then Excel will put curly brackets
'around the formula {}. You can't type these yourself. If you edit the formula
'you must enter it again with CTRL+Shift+Enter.
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.