IFERROR Excel Formula – What is it, syntax, examples and howto

Posted on March 11th, 2011 in Learn Excel - 51 comments

IFERROR Excel Formula - What is it, syntax, examples and howtoIf IFERROR() were to be a person, I would hug her so hard that Jo (my wife) would get in to a cat fight with her. I know many a woman (and man) who get in to a fight with Excel formulas often. But thankfully, we avoid that as IFERROR is not a real person. It is, however a darned useful formula.

Since I cannot hug a formula anymore than I can get my son to sit tight, I will go ahead and sing an ode to her, in my style – by writing about how useful and powerful IFERROR formula is.

What is IFERROR() Formula & How does it work?

Introduced since Excel 2007, IFERROR() formula checks a formula (or expression) and returns the value of formula if there is no error, otherwise a custom formula.

IFERROR Excel Formula - What is it, syntax, examples and howto - Chandoo.org
For eg:

=IFERROR(1/0,"Try splitting an atom instead!")

will give the message Try splitting an atom instead! because the expression 1/0 returns an error (DIV/0 error)

Where as,

=IFERROR(0/1,"Try splitting an atom instead!")

will give the value 0 since 0 divided by 1 is 0.

How does IFERROR help me?

Archimedes once said, “Give me enough data and a spreadsheet, I can make any formula return an error.”

May be he was too confident, but errors are everywhere. And that is why IFERROR is useful. It provides an elegant and simple way to tackle the errors in your workbook.

Several common uses of IFERROR are,

  1. 1. While writing lookup formulas like VLOOKUP, INDEX+MATCH it is common to search for values that do not exist in your data. You can wrap such formulas in IFERROR for peace of mind.
    Ex: =IFERROR(VLOOKUP(...),"Not found")
  2. 2. While using reference formulas like INDEX, OFFSET, frequently, we try to fetch the data that is not in the list of values. This returns #REF errors. You can fix them with IFERROR easily.
    Ex: =IFERROR(INDEX(...),"")
  3. 3. While using arithmetic, numeric expressions, usually we end up dividing by 0. You can fix such things by using IFERROR.
    Ex: =IFERROR(AVERAGE(...),"0") — Returns 0 when the list has zero values.

Things to keep in mind while IFERRORing:

Please note that IFERROR is oblivious to the type of error. That means, no matter what the error is (DIV/0, #NAME, #N/A, #REF… etc.), IFERROR treats all of them equally and shows the same value. In other words, IFERROR is like “Catch all” in programming world.

How to handle errors if you are using Excel 2003 or below?

In earlier versions of Excel, we have a formula called as ISERROR() that can check an expression or formula for error and return TRUE if so. This formula is not same as IFERROR, but we can use it along with IF() formula to get the same result. For eg.

=IF(ISERROR(VLOOKUP(...)),"Not found",VLOOKUP(...))

works same as, =IFERROR(VLOOKUP(...),"Not found")

Notice that the ISERROR approach evaluates VLOOKUP formula twice!.

Do you IFERROR?

To err is human, to IFERROR is awesome.

Ever since discovering the IFERROR feature in Excel 2007, I have been using it so often. I use it to keep my output sheets clean and my formulas simple.

What about you? Do you use IFERROR? What is your experience like? Would you also give it a hug? If so, would your spouse get in to a cat fight with it? If so can you post some pics of it on our facebook page?

Please share your experiences and tips on using IFERROR() thru comments.

Related awesomeness:

1. How to understand and handle Excel formula errors
2. Excel formulas not working? What to do?
3. Handling errors and 5 other tips for writing better VLOOKUP formulas

Your email address is safe with us. Our policies

Written by Chandoo
Tags: , , , , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

51 Responses to “IFERROR Excel Formula – What is it, syntax, examples and howto”

  1. Finnur says:

    I have no time or need for a spouse, because iferror gets all my attention. What a sweet, warm feature.

    I use it at least 6 og 7 times a week. Passionately.

  2. Pedro Wave says:

    Many colleagues are still using Excel 2003 and, when I send them formulas with IFERROR, they complain bitterly to me because the result of IFERROR is #NAME?

    • Miyo says:

      I so love this formula – just got Excel 2010 on my work computer (after avoiding 2007 like the plague) and discovered this formula , I think I need 2010 on my home computer too now. This would have saved me an hour earlier today!

  3. Keith Lewis says:

    Here is an add-in implementing IFERROR that works in Excel 2003 and earlier: http://tukhi.com/iferror.zip

  4. Bob Z says:

    If you are working with numbers, it is a good idea to add +0 at the end to convert it to a number instead of text:
    =IFERROR(AVERAGE(…),”0″) +0

  5. Patty says:

    Like the squeak on a chalkboard, I cringe everytime I see DIV/0, #NAME, #N/A, #REF in a published chart. I always re-wrote a percentage formula nested in IF statement to determine if the divisor = 0. Made for some long formulas. Now I just use IFERROR. It’s easier and my charts are clean.

    Pass it on! PLEASE!!

  6. Gregory says:

    I didn’t know this function existed until you wrote this post. Thanks for that.

    I did check out the syntax and nowhere does it require the second argument, value_if_error, to be enclosed in quotes (entered as a text) so

    =IFERROR(AVERAGE(…),0)

    will work just fine.

  7. Shairal says:

    I loved it when IFERROR was added in Excel ’07. It was so frustrating writing =IF(ISERROR(… and repeating my function twice in earlier versions. This has been a great time saver!

  8. Ken M says:

    Probably one of the best new functions of 2007 – especially to overrcome the visual clutter associated with DIV/0 where the denominator copuld be a genuine zero. I however hadn’t found ISERROR at that stage so it was indeed a lightbulb moment.

    To Pedro – tell your 2003 friends to upgrade for this and a few more lightbulb moments

  9. Jerome Ware says:

    I’ve been using iferror for a few months and luv it. Much better than using is error!

  10. Ninad Pradhan says:

    I’m changing my formulas to IFERROR to avoid the vlookup to be evaluated twice. Hopefully this can speed up the worksheet as well.

    Now, is there a way to “Automatically” get this done for all references of ISERROR using Vlookup to be changed to IFERROR with the Vlookup.

    Pardon for asking, but couldn’t resist.

    Ninad.

  11. Patty says:

    Ninad: Try using “find and replace.” Expand the window for more options and select the option to search in “formulas.” You may have to do the find and replace a few times if the need changes vary.

  12. Ninad Pradhan says:

    @Patty: Thanks. Sometimes straight forward solutions don’t come to mind. I’ll have a go at it and see the outcome.

  13. Istiyak says:

    I used it in my weekly report like if(iserror(formula),space,formula). So its return me space on error. Thank for sharing.

  14. Ashish Jain says:

    Again an awesome article with simplicity. To continue, if someone would like to wrap their existing formulas with IFERROR formula, try the macro mentioned in this article.
    http://www.excelitems.com/2011/03/wrap-iferror-iserror-formulas-add.html

  15. Christian says:

    Was indeed a lightbulb moment when I first wrapped “Iserror” around my lookup formulae.

    Is great for final presentation purposes, then coupled with conditional formatting can be a powerful audit/ review tool by colourfully highlighting these areas.

    Do not use 2007 that much – I have found the uptake of 2007 here in Australia to be fairly low in medium to large organisations, wheras home users and small business take to these new versions more easily.

    Although in the times I have used 2003 and 2007 I have used the Iferror with similar “awesome” results.

    It would form part of my Top 10 formulas list.

  16. Christian says:

    I have added the “add-in” mentioned above – can someone explain how to access and use once it is saved to the add-ins list under the Tools menu (I’m on 2003)?

    Many Thanks

  17. Keith Lewis says:

    @Christian: You can save any xll add-in to a folder, then Tools|Add-Ins… and Browse… to the folder and select the add-in. It will automatically load every time you start Excel until you deselect it in the Add-In Manager.

    If you are nervous about using add-ins from unknown sources, you can get the source code for this at http://xll.codeplex.com and build it yourself. See iferror.cpp in the utility project. You can browse the source under the Source Code tab.

    Enjoy!
    Keith

  18. Christian says:

    Thanks Keith – I have loaded it and it is in the Add-in list. So under Tools/ Add-ins, I can see it in the list and have ticked it off.

    I’m just unsure how to use it?

    What exactly does it do and how do I do it?

    Thank you for your quick response.

  19. Keith Lewis says:

    It is an Excel 2003 and earlier replacement for IFERROR. You don’t need it in 2007 or 2010. If you type ‘=IFERROR’ in a cell and hold down Ctrl and Shift while typing ‘A’ you should see the argument prompt. Something like ‘=IFERROR(Value, Value_if_error)’

  20. Christian says:

    Excellent – can see how to use it now, and it works!

    Thanks Keith

  21. Hui... says:

    Just want add a small bit to what Chandoo said “I use it to keep my output sheets clean and my formulas simple.”
    .
    I only use IFERROR on output sheets and then only sparingly.
    .
    It is important to see what your workbook is doing with your data and hiding mistakes in your workbooks logic by over use of IFERROR or other error messages can be dangerous and in fact introduce errors. I generally feel it is better to see the error propogate through to an output sheet so that at least it has a chance of being picked up.
    .
    If your logic is such that you are aware that the error is possible and want to use that logic for your model that is ok, so long as you understand what your doing.

  22. [...] Friday, we have learned about an interesting formula – IFERROR Formula using which you can easily handle errors in Excel [...]

  23. Jon says:

    I’ve been using IF(ISERROR( for many years, but never came across IFERROR before, it will certainly help make things a bit smoother.

    However if there are 2 or 3 common errors which can occur i prefer to use a nested statement with ISNA / ISBLANK etc so i can identify the original problem a bit quicker.

  24. sara says:

    Hi, i recently learned about the IFerror function but would like to know if i can use it in a data table.

    how can i use Iferror in data tables if possible?

  25. Gobish says:

    IFERROR is awesome, hats off to Microsoft and cool hug to chandoo for well explaining it. Good work

  26. Keith Lewis says:

    Hey, what about little ol’ me? Don’t I get a hug for providing a version for Excel 2003 and earlier. Not to mention the source code for it.

  27. Karla says:

    Awsome article

  28. IFERROR is well explained here , i was struggling with this for quite a long time.

  29. Ben F says:

    In the Google Docs Spreadsheet, you can use IFERROR with a single argument. it returns nothing if the argument evaluates to an error. If I enter =IFERROR(1/0) into A1 and =ISBLANK(A1) into A2, A2 evaluates as TRUE.

    Excel does not allow IFERROR to have only one argument (I’m using Excel 2008/Mac). How can I get a formula to return nothing in Excel? I know how to use conditional formatting to make a cell appear to be empty. What I want to know is how to get Excel to return a formula result that ISBLANK would evaluate as TRUE.

  30. Andy says:

    Sara

    i dont think you can use the function in a data table. However, you can use it on the outputs that drive your data table which should give you the result that you are after.

  31. Sharon says:

    Is it possible to create a nested IFERROR formula? Have a spreadsheet where I am calculating a percentage over several months. Sometimes one member may show blanks, and other times, another member may show blanks… I know that ELSE does not belong in Excel, but for the illustration purposes, I want to say:

    IFERROR(A+B+C)/(D+E+F),
    ELSE IFERROR(A+B)/(D+E),
    ELSE (A+C)/D+F)

  32. Hui says:

    @Sharon
     
    I think you want this:
    =IFERROR((A+B+C)/(D+E+F),IFERROR((A+B)/(D+E),(A+C)/(D+F))
     
    I also took the liberty of adding an extra ( in the last part
    You had: (A+C)/D+F
    I used: (A+C)/(D+F)

  33. Sharon says:

    HUI, YOU ARE A GENIUS!!  It’s not pretty, but works perfectly, so it is a thing of beauty to me. Thank you SO much!
    In case you’re interested – this is for a weighted average across 4 populations of surveys, where I must calculate all results possible.  We always get responses from the 2nd and 3rd channels (B and C), but the first channel (A) does a much lower volume of work than the others, and the 4th channel  (D) is new, so early returns are sparse.
    This means my formula must consider each of these combinations in any given month:
    A + B + C + D
    A + B + C
    B + C + D
    B + C
    The SCORE is the COUNT of Very Satisfied / TOTAL Responses
    The WEIGHT (or share of total opportunity) is COUNT of Channel Transactions / TOTAL Transactions
    The WEIGHTED AVERAGE is SCORE * WEIGHT

    So my excel formula is:

    =IFERROR((((W22/W21)*(W20/(W20+W40+W60+W80))+((W42/W41)*(W40/(W20+W40+W60+W80))+((W62/W61)*(W60/(W20+W40+W60+W80))+((W82/W81)*(W80/(W20+W40+W60+W80))))))),IFERROR(((W22/W21)*(W20/(W20+W40+W60+W80))+((W42/W41)*(W40/(W20+W40+W60+W80))+((W62/W61)*(W60/(W20+W40+W60+W80))))),IFERROR(((W42/W41)*(W40/(W20+W40+W60+W80))+((W62/W61)*(W60/(W20+W40+W60+W80))+((W82/W81)*(W80/(W20+W40+W60+W80))))),((W42/W41)*(W40/(W20+W40+W60+W80))+((W62/W61)*(W60/(W20+W40+W60+W80)))))))
    I had to let Excel figure out the parentheses…

  34. NancyInOKC says:

    Love this!   saves me more headaches than time, but makes everything prettier! 

  35. gerdami says:

    I like IFERROR(value, value_if_error).
    However, when “value” comes from an empty cell, IFERROR returns 0 while I would have wanted that the “value_if_error” be triggered.

  36. […] In fact, Chandoo once had such a crush on IFERROR that he worried his wife would become jealous of this old flame!. […]

  37. […] need to tell Excel to return $0 for those months.  To do so, you’ll use the IFERROR formula.  IFERROR works by attempting to execute a given formula; if the formula returns an error, […]

  38. […] errors are shown. If you aren’t familiar with the IFERROR() function you can read about it here. I have defined C29 as ‘Error_GameResults’ so that I can refer to it in the formula as […]

  39. anand says:

    =IFERROR(VLOOKUP(A5,G:G,1,0),”NO”)=”NO”

    if we give like i get true or false options in case i avoid the =”NO” it works i expected. but how it takes that true of false by giving “NO” at last
    thanks in advance

    • Hui... says:

      @Anand
      If you want “No” as the answer when it isn’t found
      =IFERROR(VLOOKUP(A5,G:G,1,0),”NO”)

      ps: I would always limit the size of the VLookup so use G1:G100 instead of G:G

  40. Athman says:

    I am a fan to your sense of humour

  41. Lenny says:

    Although I have no problems with IFERROR, but I’ve just learned a lot about the if function. This workshop is great: http://www.excel-aid.com/the-excel-if-function.html

  42. Paula Hofmeister says:

    I’m using INDEX(MATCH) – and am wondering how I might accomplish the following:

    I can use IFERROR to catch the errors when there is no MATCH

    =IFERROR(INDEX(CrosswalkTbl,MATCH(‘Master test’!$B2,CIPTitleTbl,0),MATCH(LogisticsMstr,CrosswalkHead,0)), “No Match”)
    When there is a MATCH, the cell I’m picking up can either be “YES” or blank – if it is blank, the formula is returning a 0 which I want to change to blank this code works to do that

    =IF(INDEX(CrosswalkTbl,MATCH(‘Master test’!$B2,CIPTitleTbl,0),MATCH(DefenseMstr,CrosswalkHead,0))”Yes”, “”)

    I have not been able to figure out how I can put these together – the IFERROR needs to come first – I’ve tried several ways to “attach” the IF statement and keeps getting invalid formula errors.

    Thanks

  43. Paula Hofmeister says:

    Correction to my previous post – the second formula is
    =IF(INDEX(CrosswalkTbl,MATCH(‘Master test’!$B2,CIPTitleTbl,0),MATCH(ProfTechMstr,CrosswalkHead,0))=”Yes”, “Yes”, “”)

    Thanks

Leave a Reply