如何在Excel中实现Sheet1与Sheet2的双向依赖下拉菜单

一、确认数据源（Sheet2） #

确保Sheet2的A列为Resource、B列为Vertical，数据无空行，关联关系准确。

二、设置Sheet1中Vertical（A列）的基础下拉菜单 #

• 选中Sheet1的目标单元格范围（如A2:A100）
• 点击「数据」选项卡 → 「数据验证」
• 在对话框中：
  • 允许：选择「序列」
  • 来源：输入公式 =UNIQUE(Sheet2!B:B)，提取Sheet2中所有唯一的Vertical值
  • 勾选「提供下拉箭头」，点击确定

三、实现选Vertical后，Resource（B列）的动态下拉 #

• 选中Sheet1对应范围的B列单元格（如B2:B100）
• 打开「数据验证」，选择「序列」
• 来源输入公式：=FILTER(Sheet2!A:A, Sheet2!B:B=Sheet1!A2)，筛选出与当前A列Vertical匹配的所有Resource
• 勾选下拉箭头，确定

四、实现选Resource后，Vertical（A列）自动匹配 #

• 选中Sheet1的A列目标单元格（如A2:A100）
• 输入公式：=XLOOKUP(Sheet1!B2, Sheet2!A:A, Sheet2!B:B, "")，根据B列选中的Resource匹配对应的Vertical，无匹配则显示空值
• 按Ctrl+Enter批量应用公式到选中范围

旧版Excel兼容方案（无动态数组函数） #

如果使用不支持UNIQUE/FILTER/XLOOKUP的旧版Excel，可通过定义名称+INDEX/MATCH实现：

1. 定义名称VerticalList：公式 =OFFSET(Sheet2!$B$1,1,0,COUNTA(Sheet2!$B:$B)-1,1)
2. 定义名称ResourceList：公式 =OFFSET(Sheet2!$A$1,1,0,COUNTA(Sheet2!$A:$A)-1,1)
3. A列数据验证来源选=VerticalList
4. B列数据验证来源输入 =INDEX(ResourceList, MATCH(Sheet1!A2, VerticalList, 0))（仅支持一对一关联）
5. A列自动匹配公式用 =INDEX(VerticalList, MATCH(Sheet1!B2, ResourceList, 0))

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