Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question
I'm not sure how to do it correctly but have a look here The last example should help – chmod Mar 8 '12 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. – Raystafarian Mar 8 '12 at 15:20
up vote 18 down vote accepted

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


share|improve this answer
That first example is exactly what I'm looking for. Thanks! – Jer Mar 8 '12 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. ;-)

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.