Google Sheets跨多表检测指定文本并返回匹配项目名实现咨询

前提假设 #

默认「Grade 4」和「Grade 5」两个工作表的所有项目名称均存储在各自的A列，若实际存储列不同，替换公式中对应的列号即可。

核心公式实现 #

C列（以B列为检索参照） #

在C2单元格输入以下公式，无需下拉，整C列会自动匹配B列对应行的检索词，两个年级表新增项目后结果也会自动同步：

=BYROW(B2:B, LAMBDA(key, 
  IF(key="",,
    TEXTJOIN(",", TRUE,
      FILTER(
        {'Grade 5'!A:A; 'Grade 4'!A:A},
        SEARCH(key, {'Grade 5'!A:A; 'Grade 4'!A:A}),
        {'Grade 5'!A:A; 'Grade 4'!A:A}<>""
      )
    )
  )
))

E列（以D列为检索参照） #

在E2单元格输入以下公式，逻辑同C列：

=BYROW(D2:D, LAMBDA(key, 
  IF(key="",,
    TEXTJOIN(",", TRUE,
      FILTER(
        {'Grade 5'!A:A; 'Grade 4'!A:A},
        SEARCH(key, {'Grade 5'!A:A; 'Grade 4'!A:A}),
        {'Grade 5'!A:A; 'Grade 4'!A:A}<>""
      )
    )
  )
))

自定义调整说明 #

• 分隔符修改：将TEXTJOIN的第一个参数","替换为你需要的分隔符即可，如需换行可替换为CHAR(10)，同时开启单元格的「自动换行」格式
• 区分大小写匹配：将SEARCH函数替换为FIND函数即可
• 完全匹配而非包含匹配：将SEARCH(key, {'Grade 5'!A:A; 'Grade 4'!A:A})替换为{'Grade 5'!A:A; 'Grade 4'!A:A}=key
• 新增检索工作表：在{'Grade 5'!A:A; 'Grade 4'!A:A}中新增对应表的列即可，比如加Grade3的话改为{'Grade 5'!A:A; 'Grade 4'!A:A; 'Grade 3'!A:A}

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