编写Google Sheets多值单元格跨表匹配查询及链接导入报错问题

需求背景 #

• OverallGroups表：A列（Team）为逗号分隔多值（如A,B,C），B列（Projects），C列（Bring）为带超链接的内容
• Jobs表：A列（Team）为单个字母，B列（Projects），需在C列返回同时满足以下条件的OverallGroups!C:C内容，且保留超链接：
  1. Jobs的单个Team值存在于OverallGroups的多值Team列表中
  2. 两表的Projects列完全匹配

现有尝试的问题总结 #

1. 原QUERY公式逻辑错误，多值匹配的正则写法不符合需求
2. TEXTJOIN无法保留单元格的超链接格式
3. BYROW+FILTER的公式误将Projects匹配条件写为匹配Bring列，且无匹配时直接返回#N/A错误
4. QUERY函数仅能提取纯文本，无法保留原单元格的超链接属性

解决方案公式 #

在Jobs表的C2单元格输入以下公式，下拉或自动填充即可：

=BYROW(A2:B,LAMBDA(row,IF(COUNTA(row)=0,"",XLOOKUP(TRUE,REGEXMATCH(OverallGroups!A:A,"(?i)\b"&INDEX(row,1)&"\b")*(OverallGroups!B:B=INDEX(row,2)),OverallGroups!C:C,"无匹配"))))

公式说明 #

• BYROW(A2:B,LAMBDA(row,...))：遍历Jobs表的每一行数据，逐行处理匹配逻辑
• IF(COUNTA(row)=0,"",...)：空行返回空文本，避免无数据时出现错误提示
• REGEXMATCH(OverallGroups!A:A,"(?i)\b"&INDEX(row,1)&"\b")：
  • (?i)：忽略大小写匹配
  • \b：单词边界，确保单个Team值（如A）不会匹配到包含它的字符串（如AB）
  • 实现单个Team值与多值列表的精确匹配
• *(OverallGroups!B:B=INDEX(row,2))：将Team匹配结果与Projects完全匹配的结果做逻辑相乘，仅返回同时满足两个条件的行
• XLOOKUP(TRUE,...)：找到第一个满足条件的行，返回对应的Bring列内容，XLOOKUP会完整保留原单元格的超链接格式
• "无匹配"：无符合条件的记录时返回的提示文本，可根据需求修改为其他内容或空文本

注意事项 #

• 若OverallGroups的Team列存在逗号后的空格（如A, B, C），需修改正则表达式为"(?i)\b\s*"&INDEX(row,1)&"\s*\b"，确保忽略空格后仍能匹配
• 若存在多个符合条件的记录，XLOOKUP仅返回第一个匹配结果；若需返回所有匹配结果且保留超链接，需结合FILTER和TEXTJOIN的替代方案（但TEXTJOIN会丢失超链接，多超链接展示需手动组合HYPERLINK函数）

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