how to switch a #N/A value into 0

[Guest]

I used the vlookup function to get pricing from a
database which is multiplied by a specific volume. When I
have no pricing in the database for a product code it
returns this #N/A. In the multiplied cells I get the same
result and the sum of the result come again this #N/A.

I have tried the if function but can't seem to turn
around to bring it to a zero value so I can still add the
result for product codes I have pricing.

Help,
Thanks in advance

André

 

[JE McGimpsey]

one way:

Assume the lookup value is in A1, and the lookup table is J1:K100:


=IF(ISNA(MATCH(A1,J1:J100,0)),0,VLOOKUP(A1,J1:K100,2,0))