# HELP: IF Statement between Dates for Reverse Index failing

Y

#### Yunus

Hi

To create different condition using a working Formulae I have
substituted IF('SHEET1'!\$K\$5:\$BT\$138<=\$IQ\$1 with IF(AND('SHEET1'!\$K\$5:
\$BT\$138>\$IQ\$1,'SHEET1'!\$K\$5:\$BT\$138<=\$IS\$1) but it then corrupts the
formulae. What am I doing wrong. I Use CTRL, SHIFT, ENTER.

In Sheet2 Cell IQ1 is =NOW()
In Sheet2 Cell IQ2 is =COUNTIF('SHEET1'!K5:BT138, "<"&TODAY())
In Sheet2 Cell IS1 is =TODAY() +14
In Sheet2 Cell IS2 is = COUNTIF('SHEET1'!K5:BT138, "<"&TODAY() +14)

The following Formulae appears to work for IF Condition >IQ1.

In Sheet2 Cell B5:B150
=IF(ROWS(\$B5:B5)>\$IQ\$2,"",INDEX('SHEET1'!\$B\$5:\$B\$138,INT(SMALL(IF
('SHEET1'!\$K\$5:\$BT\$138<=\$IQ\$1,(ROW('SHEET1'!\$K\$5:\$BT\$138)-ROW('SHEET1'!
\$K\$5)+1)*10^5+COLUMN('SHEET1'!\$K\$5:\$BT\$138)-COLUMN('SHEET1'!\$K\$5)),ROWS
(\$B5:B5))/10^5)))

In Sheet2 Cell C5:C150
=IF(ROWS(\$B5:B5)>\$IQ\$2,"",INDEX('SHEET1'!\$K\$3:\$BT\$3,MOD(SMALL(IF
('SHEET1'!\$K\$5:\$BT\$138<=\$IQ\$1,(ROW('SHEET1'!\$K\$5:\$BT\$138)-ROW('SHEET1'!
\$K\$5)+1)*10^5+COLUMN('SHEET1'!\$K\$5:\$BT\$138)-COLUMN('SHEET1'!\$K\$5)+
1),ROWS(\$B5:B5)),10^5)))

YET this set of Formula for IF Condition between Dates IQ1 & IS1 does
not work.

In Sheet2 Cell I5:I150
=IF(ROWS(\$I\$5:I5)>\$IQ\$2,"",INDEX('SHEET1'!\$B\$5:\$B\$138,INT(SMALL(IF(AND
('SHEET1'!\$K\$5:\$BT\$138>\$IQ\$1,'SHEET1'!\$K\$5:\$BT\$138<=\$IS\$1),(ROW
('SHEET1'!\$K\$5:\$BT\$138)-ROW('SHEET1'!\$K\$5)+1)*10^5+COLUMN('SHEET1'!\$K\$5:
\$BT\$138)-COLUMN('SHEET1'!\$K\$5)),ROWS(\$I\$5:I5))/10^5)))

In Sheet2 Cell J5:J50
=IF(ROWS(\$I\$5:I5)>\$IQ\$2,"",INDEX('SHEET1'!\$K\$3:\$BT\$3,MOD(SMALL(IF(AND
('SHEET1'!\$K\$5:\$BT\$138>\$IQ\$1,'SHEET1'!\$K\$5:\$BT\$138<=\$IS\$1),(ROW
('SHEET1'!\$K\$5:\$BT\$138)-ROW('SHEET1'!\$K\$5)+1)*10^5+COLUMN('SHEET1'!\$K\$5:
\$BT\$138)-COLUMN('SHEET1'!\$K\$5)+1),ROWS(\$I\$5:I5)),10^5)))