Google Sheets逗号分隔值精确匹配ARRAYFORMULA问题求解

问题根因 #

原公式使用SEARCH做子串匹配，只要单元格文本包含目标时段字符串就会判定命中，因此会出现"12PM"误命中"2PM"匹配规则的问题；SPLIT/MATCH原生不支持二维数组批量运算，直接套用会返回值错误。

可用公式 #

直接在D2单元格输入以下数组公式，自动覆盖D:O列全量数据行，新表单提交的条目会自动计算，无需手动填充：

=ARRAYFORMULA(
  IF(
    (REGEXMATCH($B$2:$B, "(^|, )"&TEXT(D$1:O$1, "H AM/PM")&"(,|$)")) * 
    (($C$2:$C = "SINGER") + ($C$2:$C = "Entertainer")),
    $A$2:$A,
    ""
  )
)

逻辑说明 #

• 正则匹配规则(^|, )时段值(,|$)强制要求匹配的时段前后要么是字符串起始位置、逗号加空格，要么是逗号、字符串结束位置，从规则上杜绝子串误匹配，比如匹配"2PM"时，不会将"12PM"中的"2PM"子串判定为有效命中
• 逻辑判断中*代表「同时满足」、+代表「满足任意一个」，只有同时符合「选中当前列对应时段」「角色为SINGER/Entertainer」两个条件时，才会返回对应行的成员姓名，否则返回空值
• 公式直接引用D1:O1的整行表头做二维批量匹配，不需要逐列修改公式参数，支持ARRAYFORMULA全量自动计算

适配调整 #

• 如果B列逗号分隔值没有后置空格（存储格式为12PM,1PM,2PM而非12PM, 1PM, 2PM），将正则规则中的, 替换为,即可，即匹配规则改为(^|,)"&TEXT(D$1:O$1, "H AM/PM")&"(,|$)
• 如果B列存储的时段文本格式和表头格式不一致，调整TEXT函数的格式编码，保证两边输出文本完全一致即可

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