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:
Error Did not find value 'me@me.com' in VLOOKUP evaluation.
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?
Update 2019-03-01: The best solution is now =IFNA(VLOOKUP(…), 0). See this other answer.
You can use the following formula. It will replace any #N/A value possibly returned by VLOOKUP(…) with 0.
=SUMIF(VLOOKUP(…),"<>#N/A")
How it works: This uses SUMIF() with only one value VLOOKUP(…) to sum up. So the result is that one value, but only if unequal to #N/A as per the condition argument. If the value equals #N/A however, the sum is zero. That's just how SUMIF() works: if no values match the conditions, it returns 0, not NULL, not #N/A.
Advantages:
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.
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.