基于层级条件的Excel IF函数状态判定问题及优化需求

我需要用A列的参考值与其他多列的层级数值对比，在B列生成对应状态。但当前使用的嵌套IF公式存在逻辑问题，输入数值后B列要么正常显示，要么返回#VALUE错误。当前使用的公式如下：

=if(and($A5<=C5,$A5>D5),C$1,if(and($A5<=D5,$A5>E5),D$1,if(and($A5<=E5,$A5>F5),E$1,if(and($A5<=F5,$A5>G5),F$1,if(and($A5<=G5,$A5>=H5),G$1,if(and($A5<H5,$A5>=I5),H$1,if(and($A5<I5,$A5>J5),I$1,if(and($A5<=J5,$A5>K5),J$1,if(and($A5<=K5,$A5>L5),K$1,if(and($A5<=L5,$A5>M5),L$1,if(and($A5<=M5,$A5>N5),M$1,if(and($A5<=N5,$A5>=O5),N$1,if(and($A5<O5,$A5>=P5),O$1,if(and($A5<P5,$A5>AC5),P$1,if(and($A5>=Q5,$A5<R5),Q$1,if(and($A5>=R5,$A5<S5),R$1,if(and($A5>=S5,$A5<T5),S$1,if(and($A5>=T5,$A5<U5),T$1,if(and($A5>=U5,$A5<V5),U$1,if(and($A5>=V5,$A5<W5),V$1,if(and($A5<=AC5),AC$1,if(and($A5>=W5,$A5<X5),W$1,if(and($A5>=X5,$A5<Y5),X$1,if(and($A5>=Y5,$A5<Z5),Y$1,if(and($A5>=Z5,$A5<AA5),Z$1,if(and($A5>=AA5,$A5<AB5),AA$1,if($A5>=AB5,AB$1,""))))))))))))))))))))))))))

1. 嵌套IF层级过多，逻辑混乱，极易出现区间重叠、条件矛盾（例如$A5<P5,$A5>AC5这类跨列的不合理范围）
2. 未对非数值型输入做错误处理，触发#VALUE错误

方案1：用LOOKUP函数简化区间匹配 #

LOOKUP适合按区间映射的场景，步骤如下：

1. 在表格空白区域（如AD:AE列）创建区间映射表，按升序排列区间上限值，对应状态取表头文本：

   区间上限	对应状态
   [C列层级值]	C$1
   [D列层级值]	D$1
   ...	...

2. 在B5单元格输入公式：

=IFERROR(LOOKUP($A5, AD$2:AD$20, AE$2:AE$20), "无效输入")

• IFERROR捕获非数值输入或无匹配的情况，返回友好提示
• 若区间是降序规则，改用数组形式的LOOKUP：

=IFERROR(LOOKUP(1, 0/(($A5<=C5)*($A5>D5)), C$1), "无匹配状态")

可依次扩展条件至所有区间。

方案2：用SWITCH替代嵌套IF #

SWITCH逻辑更直观，便于维护：

=IFERROR(SWITCH(TRUE,
  AND($A5<=C5, $A5>D5), C$1,
  AND($A5<=D5, $A5>E5), D$1,
  AND($A5<=E5, $A5>F5), E$1,
  // 按顺序补充所有正确的区间条件
  $A5>=AB5, AB$1,
  "无匹配状态"), "无效输入")

• 每个条件单独成行，逻辑清晰，易排查错误
• 优先处理范围更小的区间，避免匹配冲突

方案3：修正原嵌套IF公式 #

若坚持使用嵌套IF，先修正逻辑错误，再添加错误处理：

1. 删除或修正矛盾条件（如$A5<P5,$A5>AC5这类不合理区间）
2. 在公式最外层包裹IFERROR：

=IFERROR(原嵌套IF公式, "无效输入")

内容的提问来源于stack exchange，提问作者Aldrian Rahman Pradana