带溢出数组输入的多条件XLOOKUP：替代SUMIFS保留空值

问题核心 #

• 基于**多列溢出区域（spill ranges）**执行多条件匹配，返回溢出结果区域
• 原SUMIFS公式在无匹配项时返回0%，需改为保留空值
• 最终公式需整合进LET函数，匹配条件的溢出区域来自CHOOSECOLS输出

修正后的多条件XLOOKUP溢出公式 #

先调整你原有的XLOOKUP公式，确保条件数组维度匹配，同时让无匹配项时返回空值：

=XLOOKUP(1, ($C$3#=TRANSPOSE($C$12:$C$15)) * ($D$3#=TRANSPOSE($D$12:$D$15)), $E$12:$E$15, "", 0)

关键调整说明 #

• 用TRANSPOSE转换条件区域维度，让$C$12:$C$15/$D$12:$D$15和溢出区域$C$3#/$D$3#维度匹配，实现逐行多条件比对
• 第四参数设为""，确保无匹配项时返回空值，替代SUMIFS返回的0%

整合LET与CHOOSECOLS的优化公式 #

如果溢出区域来自CHOOSECOLS提取，用LET定义变量可简化公式、提升可读性：

=LET(
    spill_col1, CHOOSECOLS(数据源区域, 3),  // 对应原$C$3#的溢出列
    spill_col2, CHOOSECOLS(数据源区域, 4),  // 对应原$D$3#的溢出列
    lookup_col1, $C$12:$C$15,
    lookup_col2, $D$12:$D$15,
    return_col, $E$12:$E$15,
    XLOOKUP(1, (spill_col1=TRANSPOSE(lookup_col1))*(spill_col2=TRANSPOSE(lookup_col2)), return_col, "", 0)
)

额外处理：匹配到空值时保留空态 #

若return_col本身存在空单元格，需避免其被格式化为0%，可嵌套IF判断：

=LET(
    spill_col1, CHOOSECOLS(数据源区域, 3),
    spill_col2, CHOOSECOLS(数据源区域, 4),
    lookup_col1, $C$12:$C$15,
    lookup_col2, $D$12:$D$15,
    return_col, $E$12:$E$15,
    match_result, XLOOKUP(1, (spill_col1=TRANSPOSE(lookup_col1))*(spill_col2=TRANSPOSE(lookup_col2)), return_col, "", 0),
    IF(match_result=0%,"",match_result)
)

替代方案：INDEX+MATCH实现多条件溢出匹配 #

若Excel版本不支持XLOOKUP，可使用INDEX+MATCH组合实现相同效果：

=INDEX($E$12:$E$15, MATCH(1, ($C$3#=TRANSPOSE($C$12:$C$15))*($D$3#=TRANSPOSE($D$12:$D$15)), 0), "")

内容的提问来源于stack exchange，提问作者Mark S.