Excel带条件判断的联动下拉列表Data Validation公式报错问题咨询

报错原因 #

• MATCH函数参数顺序颠倒：原公式写为MATCH(Material_List;$B49;0)，不符合MATCH(查找值,查找区域,匹配模式)的语法规范，虽然单个单元格比对场景下歪打正着能返回结果，但运算逻辑异常，容易触发校验失败。
• 列序号计算逻辑错误：COLUMN(Material_List)返回的是结构化表在工作表中的绝对列号，而INDEX(Material_List[#Headers],1,列序号)要求的第三个参数是结构化表内部的相对列序号，二者数值不匹配，逻辑本身存在缺陷。
• 括号冗余：原公式末尾多了1个多余的右括号，存在语法错误。
• 数据验证的数组运算限制：Excel数据验证源对隐式数组运算的支持度极差，你用到的ISNUMBER(MATCH(...))*COLUMN(...)属于数组运算逻辑，直接填写到数据验证源中会被系统判定为无效公式。

修正方案 #

方案1：辅助列中转（兼容所有Excel版本，推荐） #

这种方法稳定性最高，不会出现兼容性问题：

1. 任选一个空白辅助列（比如当前行的Z列，Z49单元格），输入修正后的公式：

=IF(MAX((ISNUMBER(MATCH($B49;Material_List;0))*(COLUMN(Material_List)-COLUMN(Material_List[#Headers])+1)))=0;INDIRECT($C49);INDEX(Material_List[#Headers];1;MAX((ISNUMBER(MATCH($B49;Material_List;0))*(COLUMN(Material_List)-COLUMN(Material_List[#Headers])+1)))))

如果是2019及更早的Excel版本，输入完成后按Ctrl+Shift+Enter确认数组公式，365/2021版本直接回车即可。
2. 回到D49单元格的数据验证设置，Allow选项保持为List，Source中直接填写=Z49，确认后即可生成正常的联动下拉列表。

方案2：无辅助列公式（仅兼容Excel 365/2021及以上版本） #

如果不想使用辅助列，可以用BYCOL函数显式声明数组运算，直接填写到数据验证的Source中即可：

=IF(MAX(BYCOL(Material_List;LAMBDA(col;IF(ISNUMBER(MATCH($B49;col;0));COLUMN(col)-COLUMN(Material_List[#Headers])+1;0))))=0;INDIRECT($C49);INDEX(Material_List[#Headers];1;MAX(BYCOL(Material_List;LAMBDA(col;IF(ISNUMBER(MATCH($B49;col;0));COLUMN(col)-COLUMN(Material_List[#Headers])+1;0)))))

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