Finding the minimum in a range while checking for conditions to exclude cells in the range

Originally Posted by daddylonglegs

You wouldn't normally use AND as that can't return an array

You could get the minimum value in column A when column B is "x" and column C is > 100 like this with multiple IFs

=MIN(IF(B2:B100="x",IF(C2:C100>100,A2:A100)))

......or use * to replicate AND like

=MIN(IF((B2:B100="x")*(C2:C100>100),A2:A100))

both formulas need to confirmed with CTRL+SHIFT+ENTER

the first one above is probably marginally more efficient. You can add extra conditions as required

Many thanks, I should have provided more details:

I am having the following problem which I would like to solve using only excel functions without resorting to programming in vba or macros or manual commands.

I have a list of numbers in an array (a1:a700) and I am looking for the minimal number in that array that satisfies some conditions, the conditions are such that they might exclude some cells in the array. Cells in the array are excluded if their values are higher than a value stored in cell D1.
The ideal solution would have been a function like MINIF(range,condition) but such a function does not exist for MIN (it exists for counting COUNTIF, SUMIF, ...)

How do I achieve the same result using other EXCEL functions?

Keep in mind that the array length and the conditions change dynamically during the run.