Solved

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

Posted on 2016-10-04
33 Views
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.
0
Question by:route217
• 6
• 5
• 3

LVL 48

Expert Comment

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
0

Author Comment

Hi Rgonzo

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

LVL 48

Expert Comment

Could you send a sample
0

Author Comment

Tricky...to...do so..
0

LVL 48

Expert Comment

Entered as array?
0

LVL 48

Expert Comment

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)))
``````
0

Author Comment

Yes..as an array...and apologise that I cannot upload a file
0

LVL 28

Expert Comment

Formula posted by Rgonzo HERE should work for you.
0

Author Comment

Ok rgonzo I have attached a file...with test data...not sure what u may have done wrong.
test-data_.xlsx
0

LVL 28

Expert Comment

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.
0

Author Comment

Not sure what I may have done worng..typo
0

LVL 28

Assisted Solution

Subodh Tiwari (Neeraj) earned 250 total points
test-data_.xlsx
0

LVL 48

Accepted Solution

Rgonzo1971 earned 250 total points
Corrected file
test-data_V2.xlsx
0

Author Comment

Many thanks and much appreciated experts for the excellent feedback.

Posting one more question in relation to above..
0