如何高效验证Excel中类型与尺寸拼接值是否在主列表内？

1. 二级联动数据验证（最优解，从根源避免错误） #

这是性能开销最低的方案，直接在输入环节限制可选尺寸，完全不用事后计算验证：

• 第一步：整理关联表，新建工作表，列A存商品类型（如Choco Cake），列B存对应允许的尺寸（如6"、8"），同一类型对应多行尺寸（比如Choco Cake对应两行，分别填6"和8"）。
• 第二步：用名称管理器创建动态尺寸范围：选中关联表的A:B列，点击「公式」→「根据所选内容创建」，勾选「首行」，每个商品类型会自动生成对应的尺寸名称（比如Choco_Cake对应它的所有可选尺寸）。
• 第三步：设置数据验证：
  • A列（商品类型）：数据验证选「序列」，来源填UNIQUE(关联表!A:A)，生成唯一类型的下拉列表。
  • B列（尺寸）：数据验证选「序列」，来源输入=INDIRECT(SUBSTITUTE(A2," ","_"))，当A列选中某类型时，B列只能选该类型对应的尺寸，从根源杜绝错误配对。

2. 用XLOOKUP替代COUNTIF（轻量验证公式） #

如果不想调整数据验证逻辑，用XLOOKUP替换COUNTIF能大幅提升性能（尤其是主列表为Excel表格时，XLOOKUP会利用表格索引优化）：

• 替换原公式为：=IFERROR(XLOOKUP(CONCAT(A2," ",B2),Item,Item),"WRONG SIZE")
• 原理：XLOOKUP是精准定向查找，比COUNTIF的全范围遍历效率高很多，数据量越大差异越明显。

3. 条件格式提醒（仅做错误提示，不影响录入） #

如果只需要提醒错误而非阻止录入，用条件格式搭配轻量公式，比每个单元格计算返回值的开销小得多：

• 选中需要验证的单元格范围（比如B列），点击「开始」→「条件格式」→「新建规则」，选择「使用公式确定要设置格式的单元格」。
• 输入公式：=ISNA(XLOOKUP(CONCAT(A2," ",B2),Item,Item))
• 设置警示格式（比如填充红色、字体变色），错误配对的单元格会自动高亮，公式仅在需要渲染格式时计算，性能开销极低。

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