如何使用Excel公式验证嵌套列表条目均存在于主列表？

适用于Excel 365/2021（支持动态数组） #

在D2单元格输入以下公式，下拉填充即可：

=IF(AND(ISNUMBER(XMATCH(TEXTSPLIT(C2,", "),A:A))),"全部有效","存在无效条目")

公式说明： #

• TEXTSPLIT(C2,", ")：将C列单元格内的多个子条目按「逗号+空格」拆分，生成独立的条目数组。
• XMATCH(...,A:A)：检查每个拆分出的条目是否在A列主列表中存在，存在返回位置，不存在返回错误值。
• ISNUMBER(...)：将XMATCH的结果转换为布尔值（存在为TRUE，不存在为FALSE）。
• AND(...)：判断所有子条目是否都存在于主列表，全部存在则返回TRUE。
• IF(...)：根据AND的结果返回对应的验证提示。

适用于旧版Excel（无动态数组支持） #

在D2单元格输入以下数组公式，按Ctrl+Shift+Enter完成输入后下拉填充：

=IF(SUM(ISERROR(MATCH(TRIM(MID(SUBSTITUTE(C2,", ",REPT(" ",999)),(ROW(INDIRECT("1:"&LEN(C2)-LEN(SUBSTITUTE(C2,",",""))+1))-1)*999+1,999)),A:A))=0,"全部有效","存在无效条目")

公式说明： #

• SUBSTITUTE(C2,", ",REPT(" ",999))：把C列的「逗号+空格」替换为999个空格，方便后续拆分。
• MID(..., ...,999)：按固定长度截取每个子条目所在的空格段。
• TRIM(...)：去除截取内容的前后空格，得到干净的子条目。
• MATCH(...,A:A)：检查子条目是否在主列表中，不存在返回错误值。
• ISERROR(...)：标记不存在的子条目，SUM统计无效条目的数量，为0则说明所有条目都有效。

注意事项： #

• 如果C列的子条目分隔符不是「逗号+空格」，需要对应修改公式中的分隔符参数（比如TEXTSPLIT的第二个参数、SUBSTITUTE中的旧字符）。
• 确保A列主列表包含所有合法条目，重复值不影响验证结果，但建议保持主列表唯一。

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