When a cell is left blank the formula returns results

Hi,

I have created the following spreadsheet.
MySheet.png

The user inputs a list of dates in column I6 to I22 (highlighted in blue). The results are then displayed in columns L M N O P but as you can see when there is no data in column I it is returning results and I don't understand why?

Formula

=IFERROR(INDEX($B$7:$B$3000,SMALL(IF(($I$6>=$C$7:$C$3000)*($I$6<=$D$7:$D$3000)+($I$6=$E$7:$E$3000)+($I$6=$F$7:$F$3000),MATCH(ROW($B$7:$B$3000),ROW($B$7:$B$3000))),ROW(A1))),"" )

A kick in the right direction would be greatly appreciated.

Empties cells for Excel means zero(0)!

To test it type 0 in a cell(LETS SAY Q1). Then compare this cell with an emtty cell(let's say P1) . =q1=p1 Gives A TRUE as result.

So try to add an if in front of your formula.

Formula:

Please Login or Register  to view this content.

Hi.

Your criteria for determining the returns effectively consists of an "OR" statement:

IF(($I$6>=$C$7:$C$3000)*($I$6<=$D$7:$D$3000)+($I$6=$E$7:$E$3000)+($I$6=$F$7:$F$3000)

which means that a row will be considered for return if any of the following three statements is true:

1) The value in column C is <= the value in I6 AND the value in column D is >= the value in I6

2) The value in column E is = the value in I6

3) The value in column F is = the value in I6

Since the value in I6 is currently blank, and there are also rows for which the column E (or F) entries are blank, there will be some rows which satisfy your conditions.

Regards