为Array Formula添加AND条件后未按预期生效问题排查

问题背景 #

原公式可跨工作表匹配数据，返回Verified或Unverified；新增需求：当U列值为"SIM"且V列出勤状态为"No"或空白时，强制显示Unverified，但修改后该条件未生效。

核心问题点 #

• 错误使用AND函数：在ArrayFormula中，AND()是聚合函数，会将整列的布尔值合并为单个结果，无法实现逐行判断。必须用**逻辑乘（*）**替代，因为Google Sheets中TRUE*TRUE=1（等价于TRUE），其他组合为0（等价于FALSE），能实现逐行的多条件判断。
• V列判断逻辑不严谨：原条件V:V<>"yes"未考虑大小写（如单元格值为Yes/YES），且空白单元格的判断逻辑模糊，导致部分场景下无法触发预期的Unverified结果。

修正后的公式 #

同时优化了重复调用IMPORTRANGE的问题，将其提取为单独数组，减少API调用次数：

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

额外说明 #

• 用LET()函数封装重复的IMPORTRANGE和VLOOKUP结果，既提升公式可读性，又避免重复调用外部数据，减少加载时间。
• 新增条件中用lower(V:V)<>"yes"统一大小写判断，确保Yes/YES都能被识别为不符合要求的状态；V:V<>""明确匹配空白单元格的场景。

内容的提问来源于stack exchange，提问作者Mark