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é

 

[Frank Kabel]

Hi
one way:
=IF(ISNA(VLOOKUP(...)),0,VLOOKUP(...))

--
Regards
Frank Kabel
Frankfurt, Germany

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é

 

[Aladin Akyurek]

Let A2 house a product code, B2 the voulume associated with A2.

D2:

=VLOOKUP(A2,PriceTable,PriceColIdx,0)

C2:

=IF(ISNA(D2),"Product Unknown",B2*D2)

C2 is the result cell.

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é