Excel跨工作表VLOOKUP求和时避免查找不到值报错的方法

方案1：用IFERROR处理单个VLOOKUP错误 #

把每个VLOOKUP用IFERROR包裹，当找不到对应ID时返回0，避免单个错误导致整个公式失效。修改后的公式如下：

=IFERROR(VLOOKUP(B2,May!B2:D2338,3,FALSE),0)+
IFERROR(VLOOKUP(B2,April!B2:D2352,3,FALSE),0)+
IFERROR(VLOOKUP(B2,March!B2:D2387,3,FALSE),0)+
IFERROR(VLOOKUP(B2,February!B2:D2413,3,FALSE),0)+
IFERROR(VLOOKUP(B2,January!B2:D2457,3,FALSE),0)+
IFERROR(VLOOKUP(B2,December!B2:D2470,3,FALSE),0)+
IFERROR(VLOOKUP(B2,November!B2:D2522,3,FALSE),0)+
IFERROR(VLOOKUP(B2,October!B2:D2570,3,FALSE),0)+
IFERROR(VLOOKUP(B2,September!B2:D2601,3,FALSE),0)+
IFERROR(VLOOKUP(B2,August!B2:D3074,3,FALSE),0)

这个方法逻辑直观，直接基于原公式修改，适合对Excel函数不熟悉的用户。

方案2：用SUMIF简化公式（更高效） #

SUMIF本身会自动忽略不存在的ID（找不到时返回0），写法比嵌套VLOOKUP更简洁。可以直接对每个月份工作表按ID求和后汇总：

=SUM(
SUMIF(May!B:B,B2,May!D:D),
SUMIF(April!B:B,B2,April!D:D),
SUMIF(March!B:B,B2,March!D:D),
SUMIF(February!B:B,B2,February!D:D),
SUMIF(January!B:B,B2,January!D:D),
SUMIF(December!B:B,B2,December!D:D),
SUMIF(November!B:B,B2,November!D:D),
SUMIF(October!B:B,B2,October!D:D),
SUMIF(September!B:B,B2,September!D:D),
SUMIF(August!B:B,B2,August!D:D)
)

如果想进一步缩短公式，可结合INDIRECT批量生成工作表引用：

=SUM(SUMIF(INDIRECT({"May","April","March","February","January","December","November","October","September","August"}&"!B:B"),B2,INDIRECT({"May","April","March","February","January","December","November","October","September","August"}&"!D:D")))

方案3：用SUMPRODUCT批量处理（易于维护） #

如果后续需要新增月份工作表，用SUMPRODUCT配合数组形式的工作表名称，可大幅降低维护成本：

=SUMPRODUCT(IFERROR(VLOOKUP(B2,INDIRECT({"May","April","March","February","January","December","November","October","September","August"}&"!B2:D3074"),3,FALSE),0))

这里通过INDIRECT动态生成所有月份的工作表范围，IFERROR处理找不到ID的情况，最后用SUMPRODUCT完成求和。

内容的提问来源于stack exchange，提问作者Arturo Mejía