VlookUp - Trying to sum results but have #N/A

[jpnyc]

Hi,
I have Vlookup Statement, which works fine.
I am trying to sum up the results of my Vlookup, but some results return a correct #N/A when the data is not found.
What is the best way to work around this issues?

[JBeaucaire]

In Excel 2003, there's no choice but to test the data for a match first, it's basically double-dinging but what can you do?

=VLOOKUP(A1, B:C, 2, 0)

Could become:

=IF(ISERROR(VLOOKUP(A1, B:C, 2, 0)), "", VLOOKUP(A1, B:C, 2, 0))

[Palmetto]

I agree with Jbeaucaire on the returning of either blank or zero if there is no match in the VLOOKUP. However, in order to prevent double lookups as is done in nesting VLOOKUP in the IF Function, it could be more efficient to use a helper column to reference the result cell and then do the sum on the helper column.

Helper cell formula: = IF(ISNA(A1),0,A1)

[DonkeyOte]

If there's only one match you could use a SUMIF rather than a VLOOKUP - two birds etc...

The other alternative to "double dinging" using a VLOOKUP would be:

=LOOKUP(9.99E+307,CHOOSE({1,2},0,VLOOKUP(A1,B:C,2,0)))

the VLOOKUP is only evaluated once - 0 returned if no match found.