Excel数据验证+XLOOKUP问题：二级下拉列表仅显示首个值

当前状态 #

• 单元格D6的数据验证：引用A列唯一值列表，配置正常可用。
• 单元格D17的数据验证：需引用TableTest表中A列等于D6值对应的所有B列值，但当前使用公式=XLOOKUP($D6,INDIRECT("TableTest[A]"),INDIRECT("TableTest[B]"))仅显示首个值'red'，无法列出匹配的'red'和'green'。

问题原因 #

XLOOKUP函数默认仅返回第一个匹配结果，无法批量输出所有符合条件的值，因此无法满足下拉列表显示多个选项的需求。

修改方案 #

方案1：使用FILTER函数（支持动态数组的Excel版本，如365/2021） #

直接将数据验证的公式替换为：

=FILTER(TableTest[B], TableTest[A]=$D6)

该函数会自动遍历TableTest表，返回所有A列值等于D6的B列内容，动态生成完整的匹配值列表，数据验证下拉菜单会直接显示所有结果。

方案2：数组公式组合（旧版Excel，不支持动态数组） #

若使用旧版Excel，需用INDEX+SMALL+IF组合实现批量匹配，步骤如下：

1. 在空白单元格（比如E1）输入数组公式：

=INDEX(TableTest[B],SMALL(IF(TableTest[A]=$D6,ROW(TableTest[B])-ROW(TableTest[#Headers]),""),ROW(A1)))

输入完成后按Ctrl+Shift+Enter确认（旧版Excel需手动触发数组计算）。
2. 将E1单元格下拉填充至足够行数（覆盖可能的匹配结果数量），无匹配值时会显示空值。
3. 给这个单元格区域定义一个名称（比如DynamicList），然后在D17的数据验证中选择“序列”，来源选择=DynamicList。

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