My Old Friend IFERROR In #Excel

I was going through several old posts and realized that I use the IFERROR functions frequently in Excel, but had never done a post about it. So here goes!

You can download the file here and follow along. If you get a preview, look for the download arrow in the upper right hand corner.

To see IFERROR in action, you can check out several previous blog posts where I used or referenced that function:

INDEX & MATCH With IFERROR in #Excel

Pulling A Horiz List Section From A Vert In #Excel

Extracting Phone & Fax Numbers In #Excel

VLOOKUP With A Couple Twists In #Excel

Counting Labels Without Numbers In #Excel

Building A Complex Formula In #Excel

VLOOKUP Wrapped In IFNA In #Excel

Here I have a simple data range and am using the INDEX/MATCH functions to pull out the sales for a particular salesperson and month:

ife1

If I enter a person’s name that does not exist on the list, or mis-type an entry, I will get an error:

ife2

Wrapping that formula in the IFERROR function is simple to do and eliminates those unsightly error messages. The syntax for IFERROR is:

=IFERROR(value,value_if_error)

Basically, it will generate the result of the formula if there is no error, and you can define what you want it to generate if there is an error. Often we just will insert double quotes (“”) for the value_if_error portion which results in a blank, like this:

=IFERROR(INDEX(C4:N12,MATCH(C15,B4:B12,0),MATCH(C16,C3:N3,0)),””)

ife3

Alternatively, you can insert a message to instruct the user if an erroneous entry is made:

=IFERROR(INDEX(C4:N12,MATCH(C15,B4:B12,0),MATCH(C16,C3:N3,0)),”Try Again!”)

ife4

And it’s that simple. This will allow you to keep your worksheets or dashboards looking cleaner, or can be used to instruct your user what to do if an error is entered.

Happy Excelling!

Related Post

Free Download!

Subscribe to Download Your FREE Copy of
"My 70+ Favorite Excel Keyboard Shortcuts" Today!

, ,

No comments yet.

Leave a Reply