基于多条件创建IFS与INDEX MATCH公式的技术咨询

需求说明 #

现有检测数据：人群类型为吸烟者，检测数值为2.1；需结合参考表格的人群阈值，同时遵循非吸烟者规则（数值<3标记为"Action Limit"，数值>5标记为"Relocation Limit"），构建包含IFS与INDEX MATCH的多条件判断公式。

公式实现（假设参考表格结构） #

假设参考表格中：

• A列：人群类型（"吸烟者"、"非吸烟者"）
• B列：对应人群的Action Limit阈值
• C列：对应人群的Relocation Limit阈值
• 当前数据中，A2为人群类型，D2为检测数值

公式如下：

=IFS(
    A2="非吸烟者", IF(D2<3, "Action Limit", IF(D2>5, "Relocation Limit", "正常")),
    A2="吸烟者", IF(D2<INDEX($B$2:$B$3,MATCH(A2,$A$2:$A$3,0)), "Action Limit", IF(D2>INDEX($C$2:$C$3,MATCH(A2,$A$2:$A$3,0)), "Relocation Limit", "正常"))
)

公式解析 #

1. IFS分支判断：先区分人群类型，分别执行不同的判断逻辑
2. 非吸烟者逻辑：直接按给定的固定阈值（3、5）判断结果
3. 吸烟者逻辑：
   • INDEX($B$2:$B$3,MATCH(A2,$A$2:$A$3,0))：通过MATCH定位"吸烟者"在参考表中的行，再用INDEX提取对应的Action Limit阈值
   • INDEX($C$2:$C$3,MATCH(A2,$A$2:$A$3,0))：同理提取对应的Relocation Limit阈值
   • 基于提取的阈值，判断当前数值属于哪个区间，返回对应标记

实际数据代入验证 #

当A2="吸烟者"、D2=2.1时，若参考表格中吸烟者的Action Limit阈值≤2.1，Relocation Limit阈值>2.1，公式将返回"正常"；若Action Limit阈值>2.1，则返回"Action Limit"。

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