如何修改IF AND公式以正确显示会员缴费状态（已付/未付/未到账）

问题根源分析 #

原公式失效的核心原因是使用整列范围（如'Income - Membership fees'!$C:$C）进行多条件AND判断，Excel单个单元格无法正确解析整列数组的逻辑关系，导致无法精准匹配对应会员+对应月份的缴费记录，逻辑分支也存在冗余。

修改后的公式方案 #

方案1：使用XLOOKUP（适用于Excel 365/2021及以上版本） #

此方案利用复合查找键精准定位记录，逻辑清晰且运算高效：

=IF($C2<>"Monthly","",IF(E$1>TODAY(),"",LET(
    lookup_key,$A2&"|"&E$1,
    bank_status,XLOOKUP(lookup_key,'Income - Membership fees'!$C:$C&"|"&'Income - Membership fees'!$E:$E,'Income - Membership fees'!$G:$G,"NO_RECORD"),
    IF(bank_status="Yes","PAID",IF(bank_status<>"NO_RECORD","NOT IN BANK","NOT PAID"))
)))

方案2：使用INDEX+MATCH（兼容所有Excel版本） #

如果你的Excel版本不支持XLOOKUP/LET，可使用此兼容方案：

=IF($C2<>"Monthly","",IF(E$1>TODAY(),"",IF(ISERROR(MATCH(1,('Income - Membership fees'!$C:$C=$A2)*('Income - Membership fees'!$E:$E=E$1),0)),"NOT PAID",IF(INDEX('Income - Membership fees'!$G:$G,MATCH(1,('Income - Membership fees'!$C:$C=$A2)*('Income - Membership fees'!$E:$E=E$1),0))="Yes","PAID","NOT IN BANK"))))

公式逻辑说明 #

1. 过滤非月度会员：如果主表C列不是"Monthly"，直接返回空值（与年费逻辑隔离）
2. 过滤未来月份：如果当前列的月份（E$1）晚于今天，返回空值
3. 精准匹配记录：
   • 方案1：用会员姓名|缴费月份作为复合查找键，在缴费记录表中定位唯一匹配的记录
   • 方案2：用多条件MATCH找到对应记录的行号
4. 状态判断：
   • 找到记录且Showing in bank为"Yes" → PAID
   • 找到记录但Showing in bank不为"Yes" → NOT IN BANK（已上报未到账）
   • 未找到对应记录 → NOT PAID

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