求Excel中单元格逗号分隔数据与其他3单元格的缺失值计算公式

需求背景 #

B2单元格内容为[female,unspecified]，D列的D1值为male、D2为female、D3为unspecified，需获取公式计算B2相较于D1:D3的缺失值（即D列存在但B2未包含的项）。

可用公式 #

方案1（支持Excel 365/2021动态数组） #

=TEXTJOIN(", ", TRUE, FILTER(D1:D3, ISERROR(SEARCH(D1:D3, B2))))

• 逻辑：通过SEARCH检测D列每个值是否在B2中存在，ISERROR标记不存在的项，FILTER筛选出缺失项，最后用TEXTJOIN拼接结果。
• 输出结果：male

方案2（兼容旧版Excel） #

=IFERROR(INDEX(D1:D3, MATCH(TRUE, ISERROR(SEARCH(D1:D3, B2)), 0)), "")

• 逻辑：输入后按Ctrl+Shift+Enter触发数组运算，查找第一个不在B2中的D列值，IFERROR处理无缺失的情况返回空值。
• 输出结果：male

特殊场景处理（若B2的方括号为实际内容） #

如果需要精确匹配不含括号的文本，可先去除B2的方括号：

=TEXTJOIN(", ", TRUE, FILTER(D1:D3, ISERROR(SEARCH(D1:D3, SUBSTITUTE(SUBSTITUTE(B2, "[", ""), "]", "")))))

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