禁止Google表格vlookup返回的#N/A [英] Suppress #N/A returned by Google Sheets vlookup

问题描述

我有一个Google表格(示例)使用基本的vlookup创建可累加的列.对于未找到的每个搜索键，它将返回#N/A"，并将以下错误附加到这些单元格:

I have a Google Sheet (example) with a basic vlookup to create a summable column. It returns "#N/A" for every search key not found, and attaches the following error to those cells:

错误在VLOOKUP评估中找不到值"[e-mail]".

Error Did not find value '[e-mail]' in VLOOKUP evaluation.

经过大量搜索后，我发现的唯一解决方案是将vlookup包裹在IF(ISNA())中，如如何链接各种Google使用包含VLOOKUP公式的IMPORTRANGE的电子表格，而没有返回#N/A?.这行得通，但实际上我似乎不必这样做.还有另一种方法吗?

After much searching the only solution I found was to wrap the vlookup in an IF(ISNA()), given in How to link various Google spreadsheets using IMPORTRANGEs that contain VLOOKUP formulas without getting #N/A returned?. This works, but it really seems like I should not have to do this. Is there another way?

推荐答案

更新2019-03-01:最好的解决方案是=IFNA(VLOOKUP(…), 0).参见其他答案.

Update 2019-03-01: The best solution is now =IFNA(VLOOKUP(…), 0). See this other answer.

 

您可以使用以下公式.它将用0替换VLOOKUP(…)返回的#N/A值.

You can use the following formula. It will replace the #N/A values returned by VLOOKUP(…) with 0.

=SUMIF(VLOOKUP(…),"<>#N/A")

工作原理::它使用SUMIF()来汇总一个值.因此，结果是根据条件得出一个值-如果不等于#N/A.但是，如果值为#N/A，则总和为零.这就是SUMIF()的工作方式:如果没有值与条件匹配，则结果为0，而不是NULL，而不是#N/A.

How it works: This uses SUMIF() with only one value to sum up. So the result is that one value – if unequal to #N/A, according to the condition. If the value is #N/A however, the sum is zero. That's just how SUMIF() works: if no values match the conditions, the result is 0, not NULL, not #N/A.

优势:

• 与问题中引用的解决方案=IF(ISNA(VLOOKUP(…)),"",VLOOKUP(…))相比，此解决方案仅包含VLOOKUP(…)部分.这使公式更短，更简单，并且避免了仅编辑两个VLOOKUP(…)部分之一时发生的错误.

• Compared to the solution =IF(ISNA(VLOOKUP(…)),"",VLOOKUP(…)) referenced in the question, this solution contains the VLOOKUP(…) part only once. This makes the formula shorter and simpler, and avoids the mistakes that happen when editing only one of the two VLOOKUP(…) parts.

与其他答案中的解决方案=IFERROR(VLOOKUP(…))相比，错误不会被抑制，因为这会使检测和发现调试它们更加困难.仅抑制#N/A值.

Compared to the solution =IFERROR(VLOOKUP(…)) from the other answer, errors are not suppressed as that would make detecting and debugging them more difficult. Only #N/A values are suppressed.

这篇关于禁止Google表格vlookup返回的#N/A的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！