Excel表格IF跨表匹配：按间隔编号提取Sheet2对应人名

原硬编码公式的固有问题 #

• 现有公式=IF(List!B46=Master!A1147,List!A46)采用完全固定的单元格引用逻辑，每切换一个编号对应的Person列单元格，都需要手动修改两侧的行号；遇到Master表两个编号间隔不固定的场景，逐行修改不仅效率极低，还容易出现引用错位。

核心适配逻辑 #

不需要手动指定每个编号所在的固定行号，公式会自动向上查找当前Person单元格所属表格块对应的、离单元格最近的A列编号值，再用这个值去List表匹配对应人员姓名，无论两个编号之间间隔多少行文本内容，都能自动适配表格结构。

分版本通用公式写法 #

注：所有公式首次写入时，只需要把公式里的“当前行号”替换为当前要写公式的Person单元格所在的实际行号，写完后直接下拉填充到全表所有Person列单元格即可，后续不需要再手动调整任何引用

适用于Excel 365/2021及以上版本（支持XLOOKUP函数） #

=XLOOKUP(LOOKUP(9^9, $A$1:A当前行号), List!$B:$B, List!$A:$A, "")

• 逻辑拆解：
  • LOOKUP(9^9, $A$1:A当前行号)：从A列顶部一直查到当前行，自动抓取范围内最后一个数值类型的内容，也就是当前单元格上方距离最近的编号值，全程自动跳过中间的所有文本内容
  • 用抓取到的编号值，去List表的辅助编号列（B列）做精确匹配，找到后返回同行的姓名列（A列）内容，匹配不到就返回空值

适用于所有旧版Excel（2019及更早版本） #

=IFERROR(INDEX(List!$A:$A, MATCH(LOOKUP(9^9, $A$1:A当前行号), List!$B:$B, 0)), "")

• 逻辑拆解：
  • 抓取上方最近编号的逻辑和365版本完全一致
  • 通过MATCH函数定位到当前抓取的编号在List表辅助编号列的精确行号，再用INDEX函数取出该行对应的姓名值，最后通过IFERROR屏蔽匹配失败时的报错，统一返回空值

实操注意事项 #

• 一定要给公式里的范围加绝对引用锁死：$A$1的美元符号不能省，不然下拉填充的时候，查找范围的顶部会跟着当前行往下移动，就无法抓取到上方的编号
• List表的匹配范围也要做绝对引用锁定，不要使用相对引用，避免下拉填充时匹配区域错位
• 公式填充到全表后，不需要额外判断当前位置有没有对应编号，只要处于两个编号之间的区域，公式就会自动匹配上方最近的编号对应的人员姓名，完全适配间隔不固定的表格结构

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