Excel多值查找及分类求和异常求助：重复项计算有误

问题原因分析 #

你的公式仅计算其中一笔Direct Debit Gas记录，大概率是以下原因：

• 支出表G列的重复记录与查找表D列的对应值不完全匹配（比如存在大小写差异、首尾空格、特殊字符），导致其中一笔的XLOOKUP返回空值，SUM计算时自动忽略空值；
• 部分Excel版本中，XLOOKUP在数组批量查找时，对重复查找值的处理存在异常，导致未正确返回对应值。

解决方案 #

方案1：修复匹配一致性（优先推荐） #

先统一两列的匹配值格式，确保完全一致。如果无法手动统一，可修改公式为不区分大小写、忽略空格的查找：

=LET(
     _data, HSTACK(F3:F9,XLOOKUP(TRIM(LOWER(G3:G9)),TRIM(LOWER(D3:D9)),C3:C9,"")),
     _heading, TAKE(_data,,1),
     _uniq, UNIQUE(_heading),
     HSTACK(_uniq, BYROW(_uniq, LAMBDA(x, SUM((x=_heading)*TAKE(_data,,-1))))))

• 新增TRIM()去除首尾空格，LOWER()统一转为小写，避免格式差异导致的匹配失败。

方案2：简化公式逻辑，改用SUMIFS分组求和 #

替换原公式为更直观的分组求和逻辑，避免XLOOKUP数组模式的潜在问题：

=LET(
    _uniqueItems, UNIQUE(F3:F9),
    _sumResults, BYROW(_uniqueItems, LAMBDA(item, SUMIFS(C3:C9, D3:D9, XLOOKUP(item, F3:F9, G3:G9), F3:F9, item))),
    HSTACK(_uniqueItems, _sumResults)
)

• 先提取F列的唯一项，再针对每个唯一项，用SUMIFS直接匹配对应的G列值和查找表的D列值，求和C列金额。

方案3：验证匹配结果 #

如果不确定哪笔记录匹配失败，可单独添加辅助列验证XLOOKUP的返回值：
在空白单元格输入公式并下拉：

=XLOOKUP(TRIM(LOWER(G3)),TRIM(LOWER(D$3:D$9)),C$3:C$9,"未匹配")

• 查看哪笔Direct Debit Gas记录显示"未匹配"，针对性修复对应的值格式。

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