Array formula to bring back results if column DI20 onwards has a value of 1 in a cell
Posted on 2016-10-04
Hi Experts using Excel 2010
I have the following formula which works just fine….
=INDEX('Data Entry'!BF$20:BF$532,SMALL(IF('Data Entry'!$BF$20:$BF$532>=$C$1,IF('Data Entry'!$BF$20:$BF$532<=$F$1,ROW('Data Entry'!$BF$20:$BF$532)-ROW('Data Entry'!$BF$20)+1)),ROWS(A$6:A6)))
However, i want to add an additional step into the above formula so the formula also looks in column DI20 onwards and if the cell has a value of 1 return back the result.