求助：在Google Sheets中为教堂排班表构建反向匹配搜索功能

核心思路 #

通过FLATTEN将二维数据区域转为一维数组，配合FILTER筛选匹配姓名的项，再关联对应的顶部（日期）和左侧（活动）表头，最后合并三个服务工作表的结果。

单个服务工作表的搜索公式 #

假设「10.30am」工作表结构：

• 顶部行A1:Z1：日期表头
• 左侧列A2:A100：活动名称表头
• 中间区域B2:Z100：排班姓名数据

在「Information」工作表的搜索框（示例用A2单元格）输入姓名后，用以下公式返回该工作表中匹配的日期和活动：

=FILTER(
  {TRANSPOSE('10.30am'!A1:Z1), '10.30am'!A2:A100},
  EXACT(FLATTEN('10.30am'!B2:Z100), A2)
)

• FLATTEN('10.30am'!B2:Z100)：把中间的姓名数据转成一维数组，方便匹配
• EXACT(..., A2)：精确匹配搜索框中的姓名（需模糊匹配的话，替换为REGEXMATCH(FLATTEN(...), A2)）
• {TRANSPOSE(...), ...}：将日期表头转置后和活动列组合成二维数组，让FILTER能返回对应的日期和活动配对

合并三个服务工作表的完整公式 #

要同时展示三类服务的匹配结果，用VSTACK合并三个工作表的筛选结果，并添加表头和服务类型标识：

=VSTACK(
  {"日期", "活动", "服务类型"},
  FILTER(
    {TRANSPOSE('10.30am'!A1:Z1), '10.30am'!A2:A100, "10.30am"},
    EXACT(FLATTEN('10.30am'!B2:Z100), A2)
  ),
  FILTER(
    {TRANSPOSE(Praise!A1:Z1), Praise!A2:A100, "Praise"},
    EXACT(FLATTEN(Praise!B2:Z100), A2)
  ),
  FILTER(
    {TRANSPOSE('不定期服务'!A1:Z1), '不定期服务'!A2:A100, "不定期服务"},
    EXACT(FLATTEN('不定期服务'!B2:Z100), A2)
  )
)

优化：动态适配数据范围 #

如果排班数据的行数/列数会变化，用INDEX+COUNTA动态获取数据范围，避免包含空单元格：

=VSTACK(
  {"日期", "活动", "服务类型"},
  FILTER(
    {
      TRANSPOSE('10.30am'!A1:INDEX('10.30am'!1:1, COUNTA('10.30am'!1:1))),
      '10.30am'!A2:INDEX('10.30am'!A:A, COUNTA('10.30am'!A:A)),
      "10.30am"
    },
    EXACT(
      FLATTEN('10.30am'!B2:INDEX('10.30am'!B:Z, COUNTA('10.30am'!A:A)-1, COUNTA('10.30am'!1:1)-1)),
      A2
    )
  ),
  FILTER(
    {
      TRANSPOSE(Praise!A1:INDEX(Praise!1:1, COUNTA(Praise!1:1))),
      Praise!A2:INDEX(Praise!A:A, COUNTA(Praise!A:A)),
      "Praise"
    },
    EXACT(
      FLATTEN(Praise!B2:INDEX(Praise!B:Z, COUNTA(Praise!A:A)-1, COUNTA(Praise!1:1)-1)),
      A2
    )
  ),
  FILTER(
    {
      TRANSPOSE('不定期服务'!A1:INDEX('不定期服务'!1:1, COUNTA('不定期服务'!1:1))),
      '不定期服务'!A2:INDEX('不定期服务'!A:A, COUNTA('不定期服务'!A:A)),
      "不定期服务"
    },
    EXACT(
      FLATTEN('不定期服务'!B2:INDEX('不定期服务'!B:Z, COUNTA('不定期服务'!A:A)-1, COUNTA('不定期服务'!1:1)-1)),
      A2
    )
  )
)

注意事项 #

• 模糊匹配需求：把EXACT替换为REGEXMATCH即可，普通姓名无需额外正则处理
• 重复姓名处理：公式会返回所有匹配的结果行，不会遗漏重复排班

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