Excel中实现双向关联自动填充下拉单元格的需求求助

嘿，这个需求绝对可以实现！我帮你梳理一下具体的操作步骤，分分钟搞定双向关联的自动填充~

首先假设你的原始对应数据在Sheet1的A1:B2区域（A列是NAME 1，B列是NAME 2），我们要在另一张工作表（比如Sheet2）里设置双向关联的下拉菜单+自动填充：

第一步：给两列设置下拉菜单（数据验证） #

• 选中Sheet2的A列（用来填NAME 1的列），点击顶部「数据」选项卡→「数据验证」：
  • 允许类型选「序列」，来源输入=Sheet1!$A$2:$A$2（如果后续要加更多数据，直接把范围改成Sheet1!$A$2:$A$100这类的即可），点击确定。
• 同样操作Sheet2的B列（NAME 2的列），数据验证来源改成=Sheet1!$B$2:$B$2，确定。

第二步：编写双向匹配的公式 #

新版Excel（有XLOOKUP函数）： #

• 在Sheet2的B1单元格输入公式，然后下拉填充整个B列：

  =IF(A1<>"",XLOOKUP(A1,Sheet1!$A$2:$A$2,Sheet1!$B$2:$B$2,""),"")
  

• 接着在Sheet2的A1单元格输入公式，下拉填充整个A列：

  =IF(B1<>"",XLOOKUP(B1,Sheet1!$B$2:$B$2,Sheet1!$A$2:$A$2,""),"")
  

旧版Excel（没有XLOOKUP）： #

• Sheet2的B列公式用VLOOKUP+IFERROR：

  =IF(A1<>"",IFERROR(VLOOKUP(A1,Sheet1!$A$2:$B$2,2,FALSE),""),"")
  

• Sheet2的A列需要反向查找，用INDEX+MATCH组合：

  =IF(B1<>"",IFERROR(INDEX(Sheet1!$A$2:$A$2,MATCH(B1,Sheet1!$B$2:$B$2,0)),""),"")
  

小技巧：让数据源自动扩展 #

如果你后续要添加更多的NAME对应关系，建议把Sheet1的原始数据转换成Excel表格：选中数据区域→点击顶部「插入」→「表格」，勾选「表包含标题」。之后公式里的范围可以改成表格的列名，比如：

=IF(A1<>"",XLOOKUP(A1,Table1[NAME 1],Table1[NAME 2],""),"")

这样新增行的数据会自动被下拉菜单和匹配公式识别，不用手动修改范围~

备注：内容来源于stack exchange，提问作者mileswn