Google Sheets如何实现A列匹配逗号分隔数字范围后返回WIP文本值

适用场景 #

匹配A列内容到指定范围（范围单元格可存储多个用逗号分隔的数字），存在匹配时返回WIP，无匹配返回空值。

实现公式 #

单格逐行公式（适用于手动下拉填充） #

在对应结果列的第二行（如B2）输入以下公式，下拉到需要的行即可：

=IF(SUMPRODUCT(--ISNUMBER(SEARCH(","&A2&",", ","&[你的逗号分隔值范围]&",")))>0, "WIP", "")

示例：如果你的逗号分隔值存在C1到C10单元格，公式替换为：
=IF(SUMPRODUCT(--ISNUMBER(SEARCH(","&A2&",", ","&C$1:C$10&",")))>0, "WIP", "")

公式逻辑说明

• 给A列值和所有待匹配的单元格内容前后拼接逗号，避免部分匹配问题（例如A列值为1时不会误匹配到11、21这类内容）
• SEARCH函数检索A列值是否存在于对应单元格中，存在返回位置值，不存在返回错误
• ISNUMBER将检索结果转换为布尔值，匹配成功为TRUE，失败为FALSE
• 双减号将布尔值转换为数值1/0，SUMPRODUCT求和后大于0即说明存在至少一处匹配，返回WIP

整列自动溢出公式（无需手动下拉） #

如果希望A列新增内容时自动判断，直接在B2单元格输入以下溢出公式即可：

=BYROW(A2:A, LAMBDA(x, IF(x="",, IF(SUMPRODUCT(--ISNUMBER(SEARCH(","&x&",", ","&[你的逗号分隔值范围]&",")))>0, "WIP", ""))))

注意事项 #

• 需要区分匹配内容大小写时，将公式中的SEARCH替换为FIND即可
• 如果你的逗号分隔符前后带有空格，可额外嵌套TRIM函数处理，或者将","替换为", "适配格式

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