Google Sheets按匹配值提取对应行E、F列数据存入单单元格

需求说明 #

• 基础业务数据区域为表格A:F列
• H列为存储无重复值的匹配依据列
• 功能要求：以H列单元格值为匹配判断条件，筛选出A列或B列与该值匹配的所有行，将对应行的E列、F列数据提取后汇总到单个结果单元格中

现有公式与设计逻辑 #

目前可初步运行的公式为：
=IFERROR(QUERY(INDEX(SORT(FILTER({E2:E, ROW(A2:A)}, (A2:A=H2)+(B2:B=H2)), 2, 0),,1),,100000))

公式中嵌套QUERY函数，是为了规避提取内容总长度超过50000字符时可能出现的报错问题。

现有公式的功能缺陷 #

• 仅提取匹配行的E列数据，完全遗漏需求要求的F列内容
• 排序逻辑设置为行号倒序输出，和原表从上到下的常规阅读顺序不符
• 未对跨列提取内容做拼接处理，无法实现双列内容在单个单元格内的统一汇总展示

优化后可完全覆盖需求的公式 #

=IFERROR(QUERY(INDEX(SORT(FILTER({E2:E&" | "&F2:F, ROW(A2:A)}, (A2:A=H2)+(B2:B=H2)), 2, 1),,1),,100000))

调整说明 #

• 在FILTER构造的匹配数组中，将同行E列、F列内容用分隔符拼接为单条文本，解决原公式遗漏F列数据的问题
• 调整SORT的排序方向参数为升序，匹配原表自上而下的数据排列顺序
• 保留原QUERY嵌套结构，维持原有防长文本报错的能力
• 可根据展示需求自定义拼接分隔符，例如将&" | "&替换为&CHAR(10)&，即可实现单单元格内逐行展示汇总内容的效果

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