Excel中如何基于下拉列表选择实现多列关联数据自动填充？

该功能可直接在现有业务表上落地，无需复杂开发，可完美适配单负责人关联5-8列、总源数据约20列、多负责人选择的业务场景。

前置准备 #

• 先把绿色区域的原始源数据转成结构化表格：选中所有源数据区域，按Ctrl+T，勾选「表包含标题」，可在表格设计选项卡里把表命名为SourceTbl。这么做的好处是后续新增负责人、补关联数据时，公式引用范围会自动跟着更新，不用每次手动改引用区域。
• 设置K列的下拉列表：选中K列所有需要选负责人的单元格，打开「数据」选项卡的「数据验证」，允许类型选「序列」，来源直接选SourceTbl里负责人对应的整列字段，确认后就能正常做下拉选择了。

分版本实现方法 #

适配Excel 365/2021、WPS 2023及以上版本（效率最高） #

用XLOOKUP做匹配，不用逐列堆IF判断：

• 逐列手动填充：在M列第一个要出值的单元格输公式
  =XLOOKUP($K2, SourceTbl[负责人], SourceTbl[对应关联列的标题], "无匹配结果")
  *注意：这里K列的引用要锁行不锁列，也就是写成$K2的形式，写完公式后向右、向下拖填充柄，就能自动适配所有要填充的关联列，切换下拉里的负责人时会自动带出对应的值。
• 一键批量溢出：如果要一次性填5-8列的关联值，直接在M2单元格写跨列引用的公式
  =XLOOKUP(K2, SourceTbl[负责人], SourceTbl[[第一个关联列的标题]:[最后一个关联列的标题]], "无匹配结果")
  按回车后所有关联列的值会自动溢出来，连拖动公式都不用。

适配Excel 2019及更早版本 #

用VLOOKUP+MATCH组合做兼容：
在M2单元格输公式
=IFERROR(VLOOKUP($K2, 源数据绝对引用范围(比如$A:$Z), MATCH(M$1, 源数据标题行范围(比如$A$1:$Z$1), 0), FALSE), "无匹配结果")
*注意：把公式里的引用范围换成你自己表的实际范围就行，MATCH会自动定位每列要匹配的源数据序号，不用手动数源表的列数，就算后面源表调整列顺序，也不会匹配错位，写完照样向右、向下拖填充就行。

之前尝试用IF、COUNTIF组合的方案难落地，核心问题是逐行嵌套IF的写法有嵌套层数上限，每加一个负责人就要改一次公式，完全撑不起多负责人的业务规模。

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