多条件同表行列值查找：为CALL行提取对应AGENT与NURSE

需求说明 #

• 仅在CALL类型行（A列标记为CALL）的E/F列生成结果，非CALL行留空
• F列：从H列的护士列表中，提取当前呼叫（对应B列Call ID）的PARTICIPANTS中匹配到的护士（示例中Call ID 12345的F2为Kim）
• E列：先确定当前呼叫的护士在PARTICIPANTS中的位置，再提取该位置之前的最后一位属于I列代理列表的人员（示例中Call ID 12345的E2为Will）
• 注：每个Call ID的PARTICIPANTS为该CALL行下方、对应同一Call ID且A列标记为PARTICIPANT的行

公式实现（适用于Excel 365/2021） #

F列（提取护士） #

在F2单元格输入以下公式，下拉填充：

=IF(A2="CALL",LET(
    participants,FILTER($D:$D,($B:$B=B2)*($A:$A="PARTICIPANT"),""),
    INDEX(participants,XMATCH(TRUE,ISNUMBER(XMATCH(participants,$H:$H)),0))
),"")

逻辑：先筛选出当前Call ID对应的所有PARTICIPANTS，再从中找到第一个匹配H列护士列表的人员。

E列（提取转至护士的最后一位代理） #

在E2单元格输入以下公式，下拉填充：

=IF(A2="CALL",LET(
    participants,FILTER($D:$D,($B:$B=B2)*($A:$A="PARTICIPANT"),""),
    nurse_pos,XMATCH(TRUE,ISNUMBER(XMATCH(participants,$H:$H)),0),
    agent_before,FILTER(TAKE(participants,nurse_pos-1),ISNUMBER(XMATCH(TAKE(participants,nurse_pos-1),$I:$I))),
    IFERROR(INDEX(agent_before,ROWS(agent_before)),"")
),"")

逻辑：

1. 筛选当前Call ID的PARTICIPANTS
2. 定位护士在PARTICIPANTS中的位置
3. 提取该位置之前的所有人员，再筛选出其中属于I列代理列表的人员
4. 取筛选结果的最后一位，即为转至护士的代理

兼容旧版Excel的公式（非动态数组） #

F列（提取护士） #

=IF(A2="CALL",INDEX($D:$D,MIN(IF(($B:$B=B2)*($A:$A="PARTICIPANT")*ISNUMBER(MATCH($D:$D,$H:$H,0)),ROW($D:$D),99999))),"")

输入后按Ctrl+Shift+Enter作为数组公式，下拉填充。

E列（提取转至护士的代理） #

=IF(A2="CALL",INDEX($D:$D,MAX(IF(($B:$B=B2)*($A:$A="PARTICIPANT")*ISNUMBER(MATCH($D:$D,$I:$I,0))*(ROW($D:$D)<MIN(IF(($B:$B=B2)*($A:$A="PARTICIPANT")*ISNUMBER(MATCH($D:$D,$H:$H,0)),ROW($D:$D),99999))),ROW($D:$D),0))),"")

输入后按Ctrl+Shift+Enter作为数组公式，下拉填充。

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