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

route217
route217 asked
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

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

Open in new window

Regards
route217Junior

Author

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
route217Junior

Author

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

Open in new window

route217Junior

Author

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

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

Author

Commented:
Ok rgonzo I have attached a file...with test data...not sure what u may have done wrong.
test-data_.xlsx
Subodh Tiwari (Neeraj)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))),"")

Open in new window


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

Author

Commented:
Not sure what I may have done worng..typo
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015
Commented:
Please refer to the attached.
test-data_.xlsx
Top Expert 2016
Commented:
Corrected file
test-data_V2.xlsx
route217Junior

Author

Commented:
Many thanks and much appreciated experts for the excellent feedback.

Posting one more question in relation to above..