If 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.
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. 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. 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. 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
67 Responses to “IFERROR Excel Formula – What is it, syntax, examples and howto”
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.
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?
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!
Here is an add-in implementing IFERROR that works in Excel 2003 and earlier: http://tukhi.com/iferror.zip
Hello Keith,
Does only work on Excel 2007 or can it work on Excel 2003 files that contain iferror function.
Regards
Victor
Excel 2007 and above already have IFERROR. This is for Excel 2003 and below. Should even work in Excel 97.
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
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!!
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.
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!
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
I've been using iferror for a few months and luv it. Much better than using is error!
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.
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.
@Patty: Thanks. Sometimes straight forward solutions don't come to mind. I'll have a go at it and see the outcome.
I used it in my weekly report like if(iserror(formula),space,formula). So its return me space on error. Thank for sharing.
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
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.
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
@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
Hi Keith,
Thanks for your Excel 2003 IFERROR version.
However, I could not locate the code iferror.cpp in the xxx.codeplex.com website.
Hi gerdami,
It is in the http://xllutility.codeplex.com project.
Best,
Keith
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.
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)'
Excellent - can see how to use it now, and it works!
Thanks Keith
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.
[...] Friday, we have learned about an interesting formula – IFERROR Formula using which you can easily handle errors in Excel [...]
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.
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?
IFERROR is awesome, hats off to Microsoft and cool hug to chandoo for well explaining it. Good work
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.
Awsome article
IFERROR is well explained here , i was struggling with this for quite a long time.
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.
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.
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)
@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)
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...
Love this! saves me more headaches than time, but makes everything prettier!
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.
I've updated iferror.cpp in http://xllutility.codeplex.com to take an additional optional boolean argument that treats empty cells as errors if TRUE.
The default value is FALSE to preserve backward compatibility.
Cool,
You outstripped Microsoft!
[…] In fact, Chandoo once had such a crush on IFERROR that he worried his wife would become jealous of this old flame!. […]
[…] 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, […]
[…] 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 […]
=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
@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
I am a fan to your sense of humour
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
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
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
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.
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.
IFERROR doesnt work if used with OFFSET function in an array. Is there any workaround for it?
@Rohit
Can you please post a file with an example where it doesn't work ?
Here you go:
https://www.dropbox.com/s/evi0a28612besgz/Offset%20-%20IfError.xlsx#
@Rohit
Good Pickup, but remarkably the Iferror is doing what it should
If you edit the cell C9 using F2
and select the offset section OFFSET(K1,G3,0,COUNTIF(K5:K13,E4),3)
of =IFERROR(OFFSET(K1,G3,0,COUNTIF(K5:K13,E4),3),"")
then press F9
Excel displays: ={"A",1,"A";"A",2,"B";"A",3,"C"}
which is the 3 records matching Class = A
So Iferror isn't seeing an error and so doesn't get to kick in
The problem is that because the Offset() function only returns 3 entries and you have copied the formula to 5 cells, it crashes on the 4 & 5th rows
to see how to remove the errors have a read of:
http://chandoo.org/wp/2011/11/18/formula-forensics-003/
Hui...
Thank you so much! I am using conditional formatting with ISNA() to just color the cells with white font which pretty much solves my problem. Will have a look at the link you've posted.
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.
Exception: [formula] unknown function (IFERROR)
How to handle (IFERROR) formula in .xls file
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.
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)
Thanks very useful formula.....
I am breaking-up with my gf and hooking up with IFERROR.
Thank you for the help.
And, the article is splendidly written.
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
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)))
@Tommy
Please ask the question in the Chandoo.org Forums
https://chandoo.org/forum/
Please attach a sample file to assist the guys with deriving an answer for you