Excel函数求助：匹配诊断范围且满足限定条件返回指定值

适用于C2单元格，下拉填充至所有案例行：

=IF(SUMPRODUCT(--ISNUMBER(SEARCH($D$2:$D$4, B2)), --($E$2:$E$4="Y"))>0, "Y", "N")

公式说明 #

• $D$2:$D$4：绝对引用的诊断列表范围，可根据实际数据调整（比如诊断列表到D100就改为$D$2:$D$100）
• SEARCH($D$2:$D$4, B2)：检查当前案例的诊断单元格（B2）是否包含诊断列表中的任意字符串，不区分大小写；若需严格区分大小写，替换为FIND函数
• --ISNUMBER(...)：将匹配结果转换为1（匹配成功）或0（匹配失败）的数组
• --($E$2:$E$4="Y")：将诊断列表对应的SHOCK列值转换为1（值为Y）或0（值不为Y）的数组
• SUMPRODUCT(...)：将两个数组对应元素相乘后求和，只有当案例诊断包含某诊断且该诊断的SHOCK标记为Y时，对应位置乘积为1；求和结果大于0则说明存在符合条件的诊断
• IF(...)：根据求和结果返回Y（满足条件）或N（不满足条件）

示例验证 #

结合你提供的案例数据：

• 案例1：B2包含D2（SHOCK，对应E2=Y）和D3（SHOCK,PULMONARY，对应E3=Y），求和结果>0→返回Y
• 案例2：B3包含D2（SHOCK，对应E2=Y），求和结果>0→返回Y
• 案例3：B4包含D2和D3（均对应SHOCK=Y），求和结果>0→返回Y
• 案例4：B5为空，无匹配项→返回N
• 案例5：B6仅匹配D4（HYPOXIA，对应E4=N），求和结果为0→返回N

完全符合期望输出。

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