Add criteria filters to an Array formula

[3G]

Hi there-
Wondering how I can add additional criteria to an array formula like this:

=IFERROR(INDEX($C$2:$C$453,SMALL(IF($M$2:$M$453<TODAY(),ROW($M$2:$M$453)-ROW(!$M$2)+1),ROWS($M$2:$M2))),"") = C+S+E

Is it as simple as just adding "AND" to the IF?

=IFERROR(INDEX($C$2:$C$453,SMALL(IF(AND($M$2:$M$453<TODAY(),$G$2:$G$453="Active"),ROW($M$2:$M$453)-ROW(!$M$2)+1),ROWS($M$2:$M2))),"") = C+S+E

In some cases there might be up to 3 or 4 criteria.

 

[Luke M]

Not quite. Need to multiply the arrays against each other, like we sometimes use with SUMPRODUCT.
=IFERROR(INDEX($C$2:$C$453,SMALL(IF(($M$2:$M$453<TODAY())*($G$2:$G$453="Active"),ROW($M$2:$M$453)-ROW(!$M$2)+1),ROWS($M$2:$M2))),"")

The multiplication causes a single array filled with 1/0. Since anything not = 0 XL will count as True, the function works. The boolean functions AND/OR will take an array and spit out a single value.

 

[3G]

Sweet! Thanks buddy.

When I enter it as-is I get a value, but, if I Ctr+Shift+Ent it goes blank.

 

[Luke M]

Not sure what's going on with the back end of the IF function. This is what you had:
ROW($M$2:$M$453)-ROW(!$M$2)+1),ROWS($M$2:$M2))),

But I think it should be:
=IFERROR(INDEX($C:$C,SMALL(IF(($M$2:$M$453<TODAY())*($G$2:$G$453="Active"),ROW($M$2:$M$453)),ROWS($M$2:M2))),"")

Does that help?

 

[3G]

Bam! Works like a charm.

Thanks buddy!

 

[yaparala]

sheet2 having data

sheet1:-
when i enter B1 cell is 121 or 122 or 123 then under name(a3),number(b3) fileds need to fill 121 data.

example.
B1 is 121
Name Number
1 a1
2 a2
3 a3
4 a4 like this.

 

[Luke M]

Yaparala,
Rather than tag onto an existing thread with a new problem (commonly known as thread hijacking), you should start your own thread so that others will see this as a new problem that needs to be solved.