fbpx
Search
Close this search box.

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

Share

Facebook
Twitter
LinkedIn

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

Facebook
Twitter
LinkedIn

Share this tip with your colleagues

Excel and Power BI tips - Chandoo.org Newsletter

Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Excel School made me great at work.
5/5

– Brenda

Excel formula list - 100+ examples and howto guide for you

From simple to complex, there is a formula for every occasion. Check out the list now.

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Advanced Pivot Table tricks

Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

letter grades from test scores in Excel

How to convert test scores to letter grades in Excel?

We can use Excel’s LOOKUP function to quickly convert exam or test scores to letter grades like A+ or F. In this article, let me explain the process and necessary formulas. I will also share a technique to calculate letter grades from test scores using percentiles.

67 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

    • Victor Odunaiya says:

      Hello Keith,
      Does only work on Excel 2007 or can it work on Excel 2003 files that contain iferror function.
      Regards
      Victor

  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

  44. excel is my buddy says:

    Good post, but beware IFERROR abuse.

    I use INDEX+MATCH all the time. If I'm not sure that the value exists in my data, I use IF(COUNTIF()=0,,). IF the value in question doesn't exist in your data, COUNTIF will return 0; otherwise, it will >0. I usually do something like IF(COUNTIF()=0,"whatever",INDEX(MATCH( etc. obviously with the appropriate ranges/criteria. You can even check if it exists in table A using IF(COUNTIF( and then if it doesn't, you can check for table B, etc. It's useful if you want the result in table A to override the result in table B, i.e. only look in B if you can't find it in A.

    INDEX+MATCH and VLOOKUP can return #N/A for a multitude of reasons; one should not assume it's because the value doesn't exist. I know people who actually use VLOOKUP for this purpose in validations, to check if the value exists, and if it returns #N/A they assume it must be because the value doesn't exist, even though they don't know why the VLOOKUP is bombing. I call this VLOOKUP abuse. Combine it with IFERROR abuse and you'll obscure your spreadsheet.

    • Cactuscat says:

      A fabulous idea. Works great for those of us stuck using Excel 2003 as well, as IF(COUNTIF is far neater-looking, and far easier to change, than IF(ISERROR.

  45. Rohit says:

    IFERROR doesnt work if used with OFFSET function in an array. Is there any workaround for it?

  46. Bob says:

    I tried it today and am happy with my initial results.
    My formula started with an IF which is followed by OFFSET, INDEX and then MATCH functions. Happiness, placing the IFERROR before my IF removed the errors.
    What I cannot figure out is how to have the IFERROR work when I string together multiple versions of my IF, OFFSET, INDEX and MATCH formula.
    Does anybody have an idea (or two)? If I have to use ISERROR the formula will be very very long.

  47. sagar says:

    Exception: [formula] unknown function (IFERROR)
    How to handle (IFERROR) formula in .xls file

  48. Ofosu says:

    I want to find the grade of student with the following excel syntax =SMALL(K5, M5, O5, Q5,{1,2}) I want to get the first two least values in the cells. thank you.

  49. anil says:

    i am using following formula to get the last filled values of a column, but it is giving error if ther is no data , i want to use =IF(ISERROR, along with this, but not able to figure out it, any help is appreciated.

    Thanks in advance.
    my formula is =OFFSET('sheet1'!AI9,0,MATCH(MAX('sheet1'!AI9:AT9)+1,'sheet1'!AI9:AT9,1)-1)

  50. AJ says:

    Thanks very useful formula.....

  51. Vijay says:

    I am breaking-up with my gf and hooking up with IFERROR.

    Thank you for the help.
    And, the article is splendidly written.

  52. Virendra Prajapati says:

    how can extract only number?

    E2 /407 Nand Nagri
    c3/612 nand nagri shahdara
    M.S Park H-36
    1/4643 Street No 1 Ram Nagr Mandoli Shahadra
    Mansarovar park Shahdaha
    E/32 Lal Bagh Ram nager
    520/B G T Road Shadra
    96 Gali N0-1 Chandra Lok
    E / 88 / 22 Lal Bagh Mansarovar Park
    1/1134 Mansrover park Shahadra
    x-17 wecome new seelam
    H.N0-C-176 Gali N0-1 Bhagirathi Vihar
    k-423 new seelam pur
    B-3B Maha laxmi Yamuna vihar
    Indira Enclave Loni
    H No575 Street no 8 Shiv vihar
    H,N0-22 Kardam Puri
    H no178 Brijpuri Street no 3
    b-block brijpuri gali no-6 h no-241
    H,N0-814,New Mustafabad
    ambedkar vihar,johripur
    H,N0-578 G,N0-6 Old Mustafabad Tirpal Factory
    d-41 harizan basti gokol puri
    hn-51 goutam marg johri pur
    gulvatika loni
    amar vihar vikas kunj loni
    karwalnagar
    g-12 gali no-2 gayan vihar
    behta hazipur
    D-Block Vikas Kunj Loni
    gali no-2/3 h no-b-276 indra vihar,johripur
    D /1-21 East Gokul Puri Rahul Garden
    9/33.44 Mandoli
    1165 Mandoli
    Budh Bihar H,N0-1044,N0-1 Mandoli
    pardhan vali gali johripur gali no-6
    a-24 gokul puri
    H no282 Street no 4 Bank Colony
    H,N0-82 Gali N0-5 Mandoli
    gali no-16 h no-454 mandoli
    harsh vihar c2 gali no-12 h no-256
    hn-651 gali no13 mandoli
    amit vihar loni
    b-166 gali no-4 rajiv nagar
    B /257t No 15 Street no 15 Budh Vihar Mandoli

  53. Tommy says:

    Help!!!!!!!!!!
    on every month i have to add a new line to calculate the total figures
    i want a smaller formula

    my formula = =IF(ISERROR(VLOOKUP(J2,Payroll!$B$3:$AF$60,7)),"",(VLOOKUP(J2,Payroll!$B$3:$AF$60,7)))+IF(ISERROR(VLOOKUP(J2,Payroll!$B$61:$AF$120,7)),"",(VLOOKUP(J2,Payroll!$B$61:$AF$120,7)))+IF(ISERROR(VLOOKUP(J2,Payroll!$B$121:$AF$180,7)),"",(VLOOKUP(J2,Payroll!$B$121:$AF$180,7)))+IF(ISERROR(VLOOKUP(J2,Payroll!$B$181:$AF$240,7)),"",(VLOOKUP(J2,Payroll!$B$181:$AF$240,7)))+IF(ISERROR(VLOOKUP(J2,Payroll!$B$241:$AF$300,7)),"",(VLOOKUP(J2,Payroll!$B$241:$AF$300,7)))+IF(ISERROR(VLOOKUP(J2,Payroll!$B$301:$AF$360,7)),"",(VLOOKUP(J2,Payroll!$B$301:$AF$360,7)))+IF(ISERROR(VLOOKUP(J2,Payroll!$B$361:$AF$420,7)),"",(VLOOKUP(J2,Payroll!$B$361:$AF$420,7)))

Leave a Reply