For example,

Say the value of a cell is:

IF(ISNA(VLOOKUP(A3,somesheet!G:J,3,FALSE) * VLOOKUP(A3,someothersheet!A:D,4,FALSE)), 0, VLOOKUP(A3,somesheet!G:J,3,FALSE) * VLOOKUP(A3,someothersheet!A:D,4,FALSE))

Here, I want the product of the two VLOOKUPs. But some rows may not be there, so it could return NA. If it returns NA, just put zero in this cell, but otherwise I want the product.

It seems silly that you'd have to have that whole expression in there twice. Is there some shortcut where I can say "do this calculation, and if it returns a value, use that, but otherwise use some default value?

  • I'm not sure how to do it correctly but have a look here mrexcel.com/td0110.html The last example should help
    – chmod
    Commented Mar 8, 2012 at 15:19
  • Your formula is perfect, I'm not sure what shortcut you want. You could define a user defined function but that will just make it shorter, not more involved. Commented Mar 8, 2012 at 15:20
  • I would use a LET, which lets you introduce one or more variable that you can reuse in the computation. e.g. =LET(x, $B1, IF($A1 < x, x, 0)) support.microsoft.com/en-us/office/…
    – init_js
    Commented Apr 20 at 3:46

3 Answers 3


If you have Excel 2007 or later versions you can use IFERROR function to avoid repetition


or you can use a workaround like this in earlier versions


  • That first example is exactly what I'm looking for. Thanks!
    – Jer
    Commented Mar 8, 2012 at 19:06

It may make more sense to you if you checked the first lookup value for NA, and then checked the second, and if they're both valid then multiply them.

=IF(OR(ISNA(VLOOKUP(A3,somesheet!G:J,3,FALSE)),ISNA(VLOOKUP(A3,somesheet!A:D,4,FALSE))), 0, VLOOKUP(A3,somesheet!G:J,3,FALSE) * VLOOKUP(A3,somesheet!A:D,4,FALSE))

This adds more complexity, but may not offend delicate programmer sensibilities. ;-)


To eliminate the repetition of the expression, you can use the LET function in Excel. The OP's formula would then be

LET(val, VLOOKUP(A3,somesheet!G:J,3,FALSE) * VLOOKUP(A3,someothersheet!A:D,4,FALSE), IF(ISNA(val), 0, val))

I realize this solution may not have been available to the OP at the time of posting. For anyone who searches this topic going forward, you can use this with any condition, not just ISNA and ISERROR.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .