?
Solved

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

Posted on 2016-10-04
14
Medium Priority
?
54 Views
Last Modified: 2016-10-04
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
Comment
Question by:route217
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
  • 3
14 Comments
 
LVL 52

Expert Comment

by:Rgonzo1971
ID: 41827790
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
 

Author Comment

by:route217
ID: 41827811
Hi Rgonzo

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

Expert Comment

by:Rgonzo1971
ID: 41827813
Could you send a sample
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:route217
ID: 41827817
Tricky...to...do so..
0
 
LVL 52

Expert Comment

by:Rgonzo1971
ID: 41827822
Entered as array?
0
 
LVL 52

Expert Comment

by:Rgonzo1971
ID: 41827823
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
 

Author Comment

by:route217
ID: 41827825
Yes..as an array...and apologise that I cannot upload a file
0
 
LVL 32

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41827842
Formula posted by Rgonzo HERE should work for you.
0
 

Author Comment

by:route217
ID: 41827853
Ok rgonzo I have attached a file...with test data...not sure what u may have done wrong.
test-data_.xlsx
0
 
LVL 32

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41827859
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
 

Author Comment

by:route217
ID: 41827860
Not sure what I may have done worng..typo
0
 
LVL 32

Assisted Solution

by:Subodh Tiwari (Neeraj)
Subodh Tiwari (Neeraj) earned 1000 total points
ID: 41827861
Please refer to the attached.
test-data_.xlsx
0
 
LVL 52

Accepted Solution

by:
Rgonzo1971 earned 1000 total points
ID: 41827864
Corrected file
test-data_V2.xlsx
0
 

Author Comment

by:route217
ID: 41827882
Many thanks and much appreciated experts for the excellent feedback.

Posting one more question in relation to above..
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question