Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 86
  • Last Modified:

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

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
route217
Asked:
route217
  • 6
  • 5
  • 3
2 Solutions
 
Rgonzo1971Commented:
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
0
 
route217Author Commented:
Hi Rgonzo

The formula returns #num! When I drag it down the first cell returns the correct value then errors
0
 
Rgonzo1971Commented:
Could you send a sample
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
route217Author Commented:
Tricky...to...do so..
0
 
Rgonzo1971Commented:
Entered as array?
0
 
Rgonzo1971Commented:
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

0
 
route217Author Commented:
Yes..as an array...and apologise that I cannot upload a file
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Formula posted by Rgonzo HERE should work for you.
0
 
route217Author Commented:
Ok rgonzo I have attached a file...with test data...not sure what u may have done wrong.
test-data_.xlsx
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
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.
0
 
route217Author Commented:
Not sure what I may have done worng..typo
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Please refer to the attached.
test-data_.xlsx
0
 
Rgonzo1971Commented:
Corrected file
test-data_V2.xlsx
0
 
route217Author Commented:
Many thanks and much appreciated experts for the excellent feedback.

Posting one more question in relation to above..
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 6
  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now