AND condition added to Array Formula doesn't work as expected-CodePudding

I have the below array which has been working perfectly. It's comparing values from another sheet and if they match what is on this sheet , then it shows verified, otherwise it shows unverified.

=ArrayFormula(iferror(if(row(T:T)=1, "Certification Status", 
if(A:A="","",
if(lower(vlookup(T:T,importrange("abc123","Course Completion!E:H"),2,0))
&"-"&lower(vlookup(T:T,importrange("abc123","Course Completion!E:H"),3,0))
&"-"&to_date(int(vlookup(T:T,importrange("abc123","Course Completion!E:H"),4,0)))=
lower(I:I)&"-"&lower(U:U)&"-"&F:F,"Verified","Unverified")))))

I needed to add a condition to evaluate if a row value contains "SIM" and the Attendance status equals "No" or Blank then show "Unverified", in addition to the other checks. If the row value does not contain "SIM" then use the original formula shown first. However when V:V= SIM and the attendance status equals no or blank, it still shows verified.

What did I do wrong here?

=ArrayFormula(iferror(if(row(T:T)=1, "Certification Status", 
if(A:A="","",
if(and((U:U="SIM"),(V:V<>"yes"),"Unverified",
if(lower(vlookup(T:T,importrange("1A7U7OY8q4M5zCI3WLrwESMwV_471D_4kF71v30yA438","Course Completion (THINKIFIC)!E:H"),2,0))
&"-"&lower(vlookup(T:T,importrange("1A7U7OY8q4M5zCI3WLrwESMwV_471D_4kF71v30yA438","Course Completion (THINKIFIC)!E:H"),3,0))
&"-"&to_date(int(vlookup(T:T,importrange("1A7U7OY8q4M5zCI3WLrwESMwV_471D_4kF71v30yA438","Course Completion (THINKIFIC)!E:H"),4,0)))=
lower(I:I)&"-"&lower(U:U)&"-"&F:F,"Verified","Unverified"))))))

CodePudding user response：

AND & OR are not supported under ARRAYFORMULA. use * &

for OR:

if((V:V="SIM") (lower(V:V)="yes")

if you need AND you cant have the same column in both cases so this is just example if 2nd column is W and not V:

if((V:V="SIM")*(lower(W:W)="yes")