ArrayFormula from several IF statements

user_14770916 0
Viewed 36

I have a IF statement with several AND conditions working great for my needs, however i want/need to transform it into an ARRAYFORMULA but i just cannot get it to work...

Working formula is this

=if(AM3="";"";
if(and(COUNTIF(AM3;"*"&X3&"*");J3<>"x");"Arrival";
if(and(J3="x";L3<>"x";if(COUNTIF(AM3;"*"&X3&"*");false;true));"Departure";"")))

NOT working ARRAYFORMULA is this:

=arrayformula(if(AM2:AM="";"";
if(and(J2:J<>"x";COUNTIF(AM2:AM;"*"&X2:X&"*")>0);"Arrival";
if(AND(J2:J="x";L2:L<>"x";if(COUNTIF(AM2:AM;"*"&X2:X&"*")>0;FALSE;TRUE));"Departure";"d"))))

Can someone help and explain me why it doesn't work pls?

Thanks.

google-sheets-formula google-sheets
1 Answers

AND is not supported under AF. try:

=INDEX(IF(AL3:AL="";;
 IF(REGEXMATCH(LOWER(AL3:AL); LOWER(X3:X))*(J3:J<>"x"); "Send Arrival";
 IF((J3:J="x")*(L3:L<>"x")*(IF(REGEXMATCH(LOWER(AL3:AL); LOWER(X3:X)); 
 FALSE; TRUE)); "Send Departure"; ))))
user_56326290
Related