Excel 2019：基于CODE关联关系替换CODE1为对应CODE2的公式实现

核心需求回顾 #

原E列公式=INDEX($A$1:$A$16,MATCH(D2&"*",$B$1:$B$16,0))用于根据D列NUM匹配A列CODE；当匹配结果为CODE1时，若D列NUM以333391开头（对应G:H区域中CODE1关联的CODE2场景），需输出CODE2替代CODE1。

实现公式 #

将原E列公式替换为以下内容（以E2单元格为例，下拉填充即可）：

=IF(AND(INDEX($A$1:$A$16,MATCH(D2&"*",$B$1:$B$16,0))="CODE1",LEFT(D2,6)="333391"),VLOOKUP("CODE1",$G:$H,2,FALSE),INDEX($A$1:$A$16,MATCH(D2&"*",$B$1:$B$16,0)))

公式拆解说明 #

• 条件判断部分：AND(INDEX(...)="CODE1",LEFT(D2,6)="333391")
  • 第一个条件：保留原公式的匹配结果，判断是否为CODE1
  • 第二个条件：用LEFT(D2,6)提取D列NUM的前6位，判断是否符合CODE2的关联规则
• 满足条件时的输出：VLOOKUP("CODE1",$G:$H,2,FALSE)，从G:H区域中查找CODE1对应的CODE2
• 不满足条件时的输出：直接返回原公式的匹配结果，保持原CODE匹配逻辑

简化优化（可选） #

如果表格数据量较大，为避免重复计算原INDEX/MATCH公式，可借助辅助列提升效率：

1. 在F2单元格输入原公式：=INDEX($A$1:$A$16,MATCH(D2&"*",$B$1:$B$16,0))，下拉填充完成原匹配结果的存储
2. E2单元格输入简化公式：=IF(AND(F2="CODE1",LEFT(D2,6)="333391"),VLOOKUP("CODE1",$G:$H,2,FALSE),F2)

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