SubArray - Additional criteria

[Krash7000]

Hi All,

I have a subarray formula (copied from the internet and adapted), and I essentially want to add another criteria into it and don't know how.

Its an action list, and I want to have it so if "Complete" is in column K, then don't pull it through. I am not sure if it is possible, but below is the current formula I have.

=IFERROR(INDEX(List!$C$3:$C$20000,SMALL(IF(List!$G$3:$G$20000=$D$2,ROW(List!$C$3:$C$20000)-ROW(List!$C$3)+1),ROWS(List!$C$3:List!C3))),"")

Sorry it's a bit vague, but I don't understand it enough to be more specific.

Thanks in advance for any help that can be offered.

 

[barry houdini]

......I want to have it so if "Complete" is in column K, then don't pull it through....

If I get you right then you can add another IF that checks that column K isn't complete, e.g. like this:

=IFERROR(INDEX(List!$C$3:$C$20000,SMALL(IF(List!$G$3:$G$20000=$D$2,IF(List!$K$3:$K$20000<>"Complete,ROW(List!$C$3:$C$20000)-ROW(List!$C$3)+1)),ROWS(List!$C$3:List!C3))),"")

You can add more conditions in the same way but you also need an extra closing parenthesis for each

 

[Krash7000]

That worked perfectly. Exactly what I needed it to do.

Thanks very much Barry! Much appreciated.