And function in google sheets not working with complex funtions-CodePudding

=IF( K22; ARRAY_CONSTRAIN( FILTER( Pockets2.0!A:E; Pockets2.0!A:A; REGEXMATCH(Pockets2.0!C:C;"MTC"); K22=Pockets2.0!A:A; O22=IF( AND(REGEXMATCH(Pockets2.0!C:C;"MTC-.*$");TRUE()); -1; 1 ) * Pockets2.0!D:D ); 1; 5 ); )

if I quit the and and just put the first statement it works, but with 'and' and true it fails

CodePudding user response：

The and() function is an aggregating function and will not give row-by-row results in an array formula the way you seem to expect. To make it work, use Boolean arithmetic, like this:

=filter( 
  Pockets2.0!A:E, 
  K22 = Pockets2.0!A:A, 
  regexmatch(Pockets2.0!C:C, "mtc"), 
  O22 = if( regexmatch(Pockets2.0!C:C, "mtc-") * (true = true), -1, 1 ) * Pockets2.0!D:D 
)