如何快速在Excel数组中匹配指定值并提取对应上方单元格文本合并？

针对Excel 365/2021（支持动态数组） #

直接用以下公式，无需手动逐个指定Project行：

=TEXTJOIN(", ", TRUE, IF(
    (ISNUMBER(MATCH(ROW(C4:G53), SEQUENCE(ROUNDUP((52-7)/5+1,0),1,7,5), 0))
    * (C4:G53="Orientation")),
    INDEX(C4:G53, ROW(C4:G53)-ROW(C4)+1-3, COLUMN(C4:G53)-COLUMN(C4)+1),
    ""
))

公式拆解：

• SEQUENCE(ROUNDUP((52-7)/5+1,0),1,7,5)：自动生成所有Project行的行号（从7开始，步长5，到52为止），后续若Project行范围变化，只需调整括号内的起止行号（7和52）即可。
• ISNUMBER(MATCH(...))：判断当前单元格是否处于Project行内。
• *(C4:G53="Orientation")：同时筛选出内容为“Orientation”的单元格。
• INDEX(...)：提取符合条件单元格向上3行的内容，用INDEX而非OFFSET避免易失性问题。
• TEXTJOIN：将所有提取到的内容用, 分隔合并成字符串，自动忽略空值。

针对旧版Excel（不支持动态数组） #

按Ctrl+Shift+Enter输入数组公式：

=TEXTJOIN(", ", TRUE, IF(
    (ISNUMBER(MATCH(ROW(C4:G53), {7,12,17,22,27,32,37,42,47,52}, 0))
    * (C4:G53="Orientation")),
    INDEX(C4:G53, ROW(C4:G53)-ROW(C4)+1-3, COLUMN(C4:G53)-COLUMN(C4)+1),
    ""
))

也可以把Project行号做成命名范围（比如命名为ProjectRows，值设为{7,12,17,22,27,32,37,42,47,52}），让公式更简洁易维护：

=TEXTJOIN(", ", TRUE, IF(
    (ISNUMBER(MATCH(ROW(C4:G53), ProjectRows, 0))
    * (C4:G53="Orientation")),
    INDEX(C4:G53, ROW(C4:G53)-ROW(C4)+1-3, COLUMN(C4:G53)-COLUMN(C4)+1),
    ""
))

跨工作表使用 #

只需将区域引用加上工作表名称前缀，比如目标数据在Sheet2中，公式改为：

=TEXTJOIN(", ", TRUE, IF(
    (ISNUMBER(MATCH(ROW(Sheet2!C4:G53), SEQUENCE(ROUNDUP((52-7)/5+1,0),1,7,5), 0))
    * (Sheet2!C4:G53="Orientation")),
    INDEX(Sheet2!C4:G53, ROW(Sheet2!C4:G53)-ROW(Sheet2!C4)+1-3, COLUMN(Sheet2!C4:G53)-COLUMN(Sheet2!C4)+1),
    ""
))

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