# Excel array formulas to find smallest date with multiple criteria

The array formula I have will give me the smallest date from a Service table, based on the following criteria:

1) The value in the item column matches the item # in cell A1

2) The value in the amount column > 0

`=MIN(IF((IF(Service[Item]=\$A1,TRUE)*IF(Service[Amount]>0,TRUE))>0,Service[Date],"z"))`

Adding "z" was the only way I could get it to exclude 0/FALSE, which would always claim itself as the smallest value. Is there a cleaner way to calculate this? It will be part of a more complex formula.

-

Well, you should be able to use the formula without the inner `IF`s:

``````=MIN(IF((Service[Item]=\$A1)*(Service[Amount]>0),Service[Date],9^99))
``````

`(Service[Item]=\$A1)` If this is true, it automatically returns true. Same for the next statement. And when you multiply both, you get a series of 0s and 1s which excel treats as false and true respectively.

And using I'm not sure if using `z` is working for you. It's returning me `#VALUE!` apparently...

-
Thank you - this works perfectly, plus it is cleaner and easier to read. I changed the 9^99 to "z" as it is part of a bigger formula and either needs to be 0 or the minimum date value. The "z" works because MIN excludes anything that isn't a number. – user2137354 Oct 2 '13 at 17:31
@nethy Okay, as long as it works for you! ^^ – Jerry Oct 2 '13 at 18:28
In fact you don't need "z", either, you could simply leave that out, i.e. `=MIN(IF((Service[Item]=\$A1)*(Service[Amount]>0),Service[Date]))` - with that version FALSE is returned for rows where criteria doesn't match and FALSE will be ignored in this context – barry houdini Oct 3 '13 at 13:49
TRUE! haha...get it... Thanks for the tip, the formula works great and no need for the "z". – user2137354 Oct 3 '13 at 15:23