基于多列条件匹配动态列表值的Excel公式优化问题

问题背景 #

单元格区域K9:K16已通过以下公式，筛选出符合K4:K6（A列条件）和L4:L6（C列条件）的N4指定列内容并降序排序：

=SORT(
    LET(
    a;COUNTIF(K4:K6;A1:A20)+AND(K4:K6="");
    b;COUNTIF(L4:L6;C1:C20)+AND(L4:L6="");
    FILTER(FILTER(A1:J20;(A1:J1=N4);""),a*b,""));;-1)

现在需要在L9:L16实现：

• 匹配K9:K16中的结果值
• 返回N6指定列的对应内容

参考的两个公式（Option1/Option2）在K4:K6或L4:L6为空时会返回#CALC!错误，需修改以适配空条件场景。

错误核心原因 #

原参考公式未正确处理空条件即无限制、全匹配的逻辑：

• COUNTIFS在条件区域为空时会触发错误，而非默认匹配所有行
• 空条件下的XMATCH判断逻辑断裂，导致筛选结果为空

修改后的公式方案 #

优化版Option1公式 #

核心是将空条件转化为「全匹配」规则，用OR(条件区域为空, 字段匹配条件)重构筛选逻辑：

=CHOOSECOLS(
    SORT(
        FILTER(
            CHOOSECOLS(A3:I20,XMATCH(N4,A1:I1),XMATCH(N6,A1:I1)),
            (OR(COUNTA(K4:K6)=0,COUNTIF(K4:K6,A3:A20)>0))*
            (OR(COUNTA(L4:L6)=0,COUNTIF(L4:L6,C3:C20)>0))
        );;-1
    );2)

• 用COUNTA(K4:K6)=0判断条件区域是否为空，为空则直接视为匹配
• 用*实现两个条件的「且」逻辑，和K9:K16原公式的a*b逻辑保持一致

优化版Option2公式 #

修复空条件下的XMATCH判断，简化冗余逻辑同时保留重复值偏移处理：

=LET(
     a, K9:K16,
     b, A1:I1,
     c, A3:I20,
     d, XLOOKUP(N4,b,c,""),
     condA, OR(COUNTA(K4:K6)=0,NOT(ISNA(XMATCH(A3:A20,K4:K6)))),
     condC, OR(COUNTA(L4:L6)=0,NOT(ISNA(XMATCH(C3:C20,L4:L6)))),
     MAP(a,LAMBDA(α, 
         @DROP(
             TOCOL(
                 FILTER(CHOOSECOLS(c,XMATCH(N6,b)),(d=α)*condA*condC,""),
                 3
             ),
             COUNTIF(K9:α,α)-1
         )
     ))
)

• 新增condA/condC变量统一处理空条件：条件区域为空时直接返回TRUE
• 用CHOOSECOLS直接提取目标列，简化原IFS的冗余判断
• 保留原重复值偏移逻辑，确保相同结果返回对应行的N6列内容

验证要点 #

• 当K4:K6或L4:L6为空时，公式自动视为「不限制该条件」，返回所有符合另一条件的结果
• 条件区域有值时，逻辑和原公式一致，筛选匹配内容
• 重复值场景下仍能正确返回对应行的目标列内容

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