Double formula for the same cell

[seriousdamage]

Hi, I have a vlookup formula in one cell :
=(VLOOKUP(D7,'Foods Database'!A:D,4,0)*E7)

The formula looks into one sheet to match a food name,
and reports the amount of protein the food has
everything is then multiplied by the cell containing the quantity. (E7)

Sometimes the lookup reports "#N/A"

I would like to add to the above formula that if the result of the lookup is "#N/A" than
in the cell replace #N/A with "0" (zero)

Can I add a second formula in the same cell?

Many Thanks

 

[Comfy]

=IF(ISNA(VLOOKUP(D7,'Foods Database'!A:D,4,0)),0,VLOOKUP(D7,'Foods Database'!A:D,4,0)*E7)

 

[Robert Mika]

In Excel 2007 and 2010 you can use:
=IFERROR(VLOOKUP(D7,'Foods Database'!A:D,4,0)*E7,0)
That will replace any error with 0.

 

[T. Valko]

Hi, I have a vlookup formula in one cell :
=(VLOOKUP(D7,'Foods Database'!A:D,4,0)*E7)

The formula looks into one sheet to match a food name,
and reports the amount of protein the food has
everything is then multiplied by the cell containing the quantity. (E7)

Sometimes the lookup reports "#N/A"

I would like to add to the above formula that if the result of the lookup is "#N/A" than
in the cell replace #N/A with "0" (zero)

Can I add a second formula in the same cell?

Many Thanks

One way...

Assuming that the food name will not appear more than once (if at all) in your data table.

=SUMIF('Foods Database'!A:A,D7,'Foods Database'!D:D)*E7