We help IT Professionals succeed at work.

# Array formula to bring back results if column DI20 onwards has a value of 1 in a cell

on
Hi Experts using Excel 2010

I have the following formula which works just fine….

Array Formula
=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.
Comment
Watch Question

## View Solutions Only

Top Expert 2016

Commented:
Hi,

pls try

``````=INDEX('Data Entry'!BF\$20:BF\$532,SMALL(IF(AND('Data Entry'!\$DI\$20:\$DI\$532=1,'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)))
``````
Regards
Junior

Commented:
Hi Rgonzo

The formula returns #num! When I drag it down the first cell returns the correct value then errors
Top Expert 2016

Commented:
Could you send a sample
Junior

Commented:
Tricky...to...do so..
Top Expert 2016

Commented:
Entered as array?
Top Expert 2016

Commented:
Maybe
``````=INDEX('Data Entry'!BF\$20:BF\$532,SMALL(IF('Data Entry'!\$DI\$20:\$DI\$532=1,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)))
``````
Junior

Commented:
Yes..as an array...and apologise that I cannot upload a file
Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
Formula posted by Rgonzo HERE should work for you.
Junior

Commented:
Ok rgonzo I have attached a file...with test data...not sure what u may have done wrong.
test-data_.xlsx
Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
You need to wrap the formula within IFERROR like this....
``````=IFERROR(INDEX('Data Entry'!A\$2:A\$6,SMALL(IF('Data Entry'!\$DI\$2:\$DI\$6=1,IF('Data Entry'!\$BF\$2:\$BF\$6>=\$C\$1,IF('Data Entry'!\$BF\$2:\$BF\$6<=\$F\$1,ROW('Data Entry'!\$BF\$2:\$BF\$6)-ROW('Data Entry'!\$BF\$2)+1))),ROWS(A\$3:A3))),"")
``````

The column DI on Data Entry Sheet doesn't meet the criteria i.e it is blank and it doesn't contain 1 in any of its cell.
Junior

Commented:
Not sure what I may have done worng..typo
Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015
Commented: