how to add a filter criteria to an existing array formula?

Hello, good day to you all.

First of all, I'm sorry for my bad english.

So, I have this array formula :

{=IFERROR(INDEX(data1!$B$2:$B$1354,SMALL(IF(data1!$A$2:$A$1354=form1!$F$10,ROW(data1!$B$2:$B$1354)-ROW(data1!B$2)+1),ROWS(data1!B$2:data1!B2))),"")}

and, I would like to add a criteria to mentioned array formula.

My question is, how?

Note:
I've included the example.
The array formula is located in "form1" sheet, in the "number" section of the table. The table is based on the data in "data1" sheet
What I want to achieve is :
how to exclude the number list that has a "Deceased" and "Moved" status?

You can put this array* formula in C20 of the Form1 sheet:

=IFERROR(INDEX(data1!$B$2:$B$1354,SMALL(IF((data1!$A$2:$A$1354=$F$10)*(data1!$AK$2:$AK$1354<>"Moved")*(data1!$AK$2:$AK$1354<>"Deceased"),ROW(data1!$B$2:$B$1354)-ROW(data1!B$2)+1),ROWS($2:2))),"")

(I've shown the changes in red).

*Use Ctrl-Shift-Enter to confirm, then copy into C21:C29.

Hope this helps.

Pete

Why not include only "Active" ? Or are there other "Status" values?

Hi Pete_UK.
The formula works great. Thank you.
If I want to add another criteria, like for example :
{=IFERROR(INDEX(data1!$B$2:$B$1354,SMALL(IF((data1!$A$2:$A$1354=$F$10)*(data1!$AK$2:$AK$1354<>"Moved")*(data1!$AK$2:$AK$1354<>"Deceased")*(data1!$AH$2:$AH$1354<>"prof2"),ROW(data1!$B$2:$B$1354)-ROW(data1!B$2)+1),ROWS($2:5))),"")}
is it like that? (I've tried but it didn't works) [i've edit some of the data from "prof1" to "prof2"]
If you would be so kind to explain the added formula? (what is the "*" and "<>" means).

Hi JohnTopley.
The "Status" values are only 3: "Active", "Deceased", and "Moved".
I don't know how to include only "Active" formula.
I've tried using VLOOKUP but it did not work. I don't quite understand how formula used in array formula.

For "Active"

and column AJ for "Prof2"


=IFERROR(INDEX(data1!$B$2:$B$1354,SMALL(IF((data1!$A$2:$A$1354=$F$10)*(data1!$AK$2:$AK$1354="Active")*(data1!$AJ$2:$AJ$1354<>"prof2"),ROW(data1!$B$2:$B$1354)-ROW(data1!B$2)+1),ROWS($2:2))),"")

The "*" is equivalent to AND for arrays, and the "<>" means "not equal to". I didn't know if you had more status values, as your sample only contained 6 example sets of data, but you could write the formula like this:

=IFERROR(INDEX(data1!$B$2:$B$1354,SMALL(IF((data1!$A$2:$A$1354=$F$10)*(data1!$AK$2:$AK$1354="Active"),ROW(data1!$B$2:$B$1354)-ROW(data1!B$2)+1),ROWS($2:2))),"")

i.e. you are checking for "equals Active" rather than "not equal to Moved AND not equal to Deceased" - as well as the formula being shorter, it will execute more quickly as there are fewer conditions to check through.

In your example file the values "prof1" occur in column AJ not in column AH so you should adjust the references accordingly, and rather than "not equal to prof2" (which will include all other values) you might like to make it "equals prof1" (so only those values will be included).

Hope this helps.

Pete

Hi JohnTopley and Pete_UK. Sorry for the late reply.

Thank you JohnTopley and Pete_UK for the assistant. So sorry for my mistake in previous formula.
And special thanks to Pete_UK, for the explanation on the formula and education. It's super helpful
I need to read and practice more often to get the hang of array formula.
Thank you again guys.

Oh yeah, could you guys recommend me some good reading about array formulas?