Adding a Second Condition to a Return List Array Formula

[Aimee S.]

Greetings Folks!

Any notion of how I would add the criteria of 'Query 2 Data (Child Projects)'!$I$4:$I$40000<>"Cancelled" to this existing array in a file I have taken ownership of (and am not too familiar with how this functions)?

{=IFERROR(INDEX('Query 2 Data (Child Projects)'!$E$4:$E$40000,SMALL(IF('Query 2 Data (Child Projects)'!$B$4:$B$40000='Budget-Driven Spread Calculator'!$B$7,ROW('Query 2 Data (Child Projects)'!$B$4:$B$40000)-ROW($B$4)+1),ROWS($B$4:$B4))),"")}

Thanks in advance for your time and knowledge!!!!
Warmly,
Aimee

 

[Fluff]

Clear the all the formulae from that column & replace it with

=FILTER('Query 2 Data (Child Projects)'!$E$4:$E$40000,('Query 2 Data (Child Projects)'!$B$4:$B$40000=$B$7)*('Query 2 Data (Child Projects)'!$I$4:$I$40000<>"Cancelled"))

 

[KRice]

@Fluff, the only potential issue I see is that $B$7 may need to be revised to 'Budget-Driven Spread Calculator'!$B$7...but that would not be necessary if the intended B7 reference is on the same sheet as the formula.

 

[Aimee S.]

Thank you both for your responses but that did not solve the issue. Fluff's suggestion provides repeating rows of one project ID whereas the old formula provides a list smallest to largest of 7 or so project IDs. What I did was add to the SQL the selected column
, case when p.prj_clarity_status = 'Cancelled' then null
else p.prj_id end "Child Project ID Cancelled Excluded"
Then I updated the formula in the OP and referenced that new helper column and the listed project IDs now provide only IDs associated with investments that are not cancelled.

Apologies if the OP was not clear enough and thanks again for looking! It is always fun to inherit files with other mind's approaches to formulas at our job heh. I use the term 'fun' quite loosely.
Cheers.

 

[Aimee S.]

I also fixed the OP formula in the person's file I was given as it had a couple of errors.
=IFERROR(INDEX('Query 2 Data (Child Projects)'!$F$4:$F$40000,SMALL(IF('Query 2 Data (Child Projects)'!$B$4:$B$40000='Budget-Driven Spread Calculator'!$B$7,ROW('Query 2 Data (Child Projects)'!$B$4:$B$40000)-ROW('Query 2 Data (Child Projects)'!$B$4)+1),ROWS('Query 2 Data (Child Projects)'!$B$4:$B4))),"")

 

[Fluff]

Fluff's suggestion provides repeating rows of one project ID

Did you clear all the formula & then put my suggestion in the 1st cell only?
Also the formula should be normally entered, ie do not use Ctrl Shift Enter.

 

[kvsrinivasamurthy]

Try

=IFERROR(INDEX('Query 2 Data (Child Projects)'!$E$4:$E$40000,SMALL(IF(('Query 2 Data (Child Projects)'!$B$4:$B$40000='Budget-Driven Spread Calculator'!$B$7)*('Query 2 Data (Child Projects)'!$I$4:$I$40000<>"Cancelled" ),ROW('Query 2 Data (Child Projects)'!$B$4:$B$40000)-ROW($B$4)+1),ROWS($B$4:$B4))),"")}

 

[Aimee S.]

Sorry Fluff!!! I gave it another go. No repeating rows, and it works great. Some days.... I wonder how I managed to get dressed. lol

 

[Fluff]

Glad we could help & thanks for the feedback.