ArtsAutosBooksBusinessEducationEntertainmentFamilyFashionFoodGamesGenderHealthHolidaysHomeHubPagesPersonal FinancePetsPoliticsReligionSportsTechnologyTravel

How to Use the IFERROR Function in Excel

Updated on January 8, 2019
THEAMAZINGJAMES profile image

James likes to learn about technology and share what he learns through his articles.

IFERROR

The IFERROR function can be used in wide variety of situations. There are seven different types of errors that can occur. With this function you can change the perception that there are imperfections in your spreadsheet.
The IFERROR function can be used in wide variety of situations. There are seven different types of errors that can occur. With this function you can change the perception that there are imperfections in your spreadsheet.

Description of the IFERROR Function

The IFERROR function will return a value that you specify if an error occurs in a cell. There are numerous errors in Microsoft Excel that can be removed with the IFERROR function and are described below:

  • #N/A – Means that a function or a formula can’t find data that is referenced
  • #VALUE! – In this case the wrong type of function or operand has been used in an argument
  • #REF! – Means that a specific reference is not valid within a function or argument
  • #DIV/0! – Means that a function or operation is trying to divide by zero
  • #NUM! – Occurs when a formula has an invalid numeric value for the given operation
  • #NAME? – This appears when text within a formula is not recognized
  • #NULL! – Occurs in a formula when a space is used that references multiple ranges and when a comma segregates range references

If any of these errors occur, the IFERROR function can replace the error with any value that you would like to appear in the cell. Additionally, you can also display no value at all. Note that the function does not have to be used only when there is going to be an error. As the name suggests (IFERROR), the value will only be returned if an error occurs.

Syntax of the IFERROR Function

To be able to use the IFERROR function, the syntax of the function must be followed perfectly. Below the caption, the proper syntax is displayed. The syntax contains the "Value" argument and the "Value_if_error" argument which are both required for the function to work. The value argument is the value that is checked for an error. An operation that could have an error would be placed here. Next, the value if error is what will be returned if there is in fact an error. If there is not an error, the operation will resume as normal. Let's look at an example.

IFERROR Syntax

IFERROR(value, value_if_error)

Example 1 VLOOKUP REF Error

In the example below, I use the VLOOKUP function and show that the #NA error appears if the function cannot reference a cell. In the first row of the columns labeled Revenue 2017, the function is searching for the date 3 columns to the left. Once the VLOOKUP function finds the date, it can return the specified revenue value. Since the lookup value for the VLOOKUP function is unavailable the #NA is returned.

Lookup Table Missing Lookup Value

The IFERROR function is almost routinely used with the VLOOKUP function if there is any chance the lookup table could be waiting on data or missing data in general.
The IFERROR function is almost routinely used with the VLOOKUP function if there is any chance the lookup table could be waiting on data or missing data in general.

#NA Error Returned

Resolution

Say we don't want the error to appear and would like to have a blank space to alleviate any possible confusion. This can be accomplished with the IFERROR function. The function that contained the VLOOKUP with an error is shown below. The function that follows includes the IFERROR function to remove the error.

With error:

  • =VLOOKUP(AI8,AF6:AG30,2,FALSE)

Error removed:

  • =IFERROR(VLOOKUP(AI8,AF6:AG30,2,FALSE),"")

When using this IFERROR function in this case, the VLOOKUP function in its entirety is the value argument with the equals sign removed. Next, a comma needs to be added to separate the arguments. Next the value that you want to return needs to be added. Since I want a blank space I use quotes, then close the function with parenthesis. You can return any text that you want by placing that text within the quotes. If you would like a number value to appear, the quotes are unnecessary.

References

Microsoft. (n.d.). IFERROR function. Retrieved January 7, 2019, from https://support.office.com/en-us/article/IFERROR-function-C526FD07-CAEB-47B8-8BB6-63F3E417F611

Bluttman, K. (n.d.). Excel Error Messages to Get to Know. Retrieved January 7, 2019, from https://www.dummies.com/software/microsoft-office/excel/excel-error-messages-to-get-to-know/

© 2019 James Smith

Comments

    0 of 8192 characters used
    Post Comment

    No comments yet.

    working

    This website uses cookies

    As a user in the EEA, your approval is needed on a few things. To provide a better website experience, hubpages.com uses cookies (and other similar technologies) and may collect, process, and share personal data. Please choose which areas of our service you consent to our doing so.

    For more information on managing or withdrawing consents and how we handle data, visit our Privacy Policy at: https://maven.io/company/pages/privacy

    Show Details
    Necessary
    HubPages Device IDThis is used to identify particular browsers or devices when the access the service, and is used for security reasons.
    LoginThis is necessary to sign in to the HubPages Service.
    Google RecaptchaThis is used to prevent bots and spam. (Privacy Policy)
    AkismetThis is used to detect comment spam. (Privacy Policy)
    HubPages Google AnalyticsThis is used to provide data on traffic to our website, all personally identifyable data is anonymized. (Privacy Policy)
    HubPages Traffic PixelThis is used to collect data on traffic to articles and other pages on our site. Unless you are signed in to a HubPages account, all personally identifiable information is anonymized.
    Amazon Web ServicesThis is a cloud services platform that we used to host our service. (Privacy Policy)
    CloudflareThis is a cloud CDN service that we use to efficiently deliver files required for our service to operate such as javascript, cascading style sheets, images, and videos. (Privacy Policy)
    Google Hosted LibrariesJavascript software libraries such as jQuery are loaded at endpoints on the googleapis.com or gstatic.com domains, for performance and efficiency reasons. (Privacy Policy)
    Features
    Google Custom SearchThis is feature allows you to search the site. (Privacy Policy)
    Google MapsSome articles have Google Maps embedded in them. (Privacy Policy)
    Google ChartsThis is used to display charts and graphs on articles and the author center. (Privacy Policy)
    Google AdSense Host APIThis service allows you to sign up for or associate a Google AdSense account with HubPages, so that you can earn money from ads on your articles. No data is shared unless you engage with this feature. (Privacy Policy)
    Google YouTubeSome articles have YouTube videos embedded in them. (Privacy Policy)
    VimeoSome articles have Vimeo videos embedded in them. (Privacy Policy)
    PaypalThis is used for a registered author who enrolls in the HubPages Earnings program and requests to be paid via PayPal. No data is shared with Paypal unless you engage with this feature. (Privacy Policy)
    Facebook LoginYou can use this to streamline signing up for, or signing in to your Hubpages account. No data is shared with Facebook unless you engage with this feature. (Privacy Policy)
    MavenThis supports the Maven widget and search functionality. (Privacy Policy)
    Marketing
    Google AdSenseThis is an ad network. (Privacy Policy)
    Google DoubleClickGoogle provides ad serving technology and runs an ad network. (Privacy Policy)
    Index ExchangeThis is an ad network. (Privacy Policy)
    SovrnThis is an ad network. (Privacy Policy)
    Facebook AdsThis is an ad network. (Privacy Policy)
    Amazon Unified Ad MarketplaceThis is an ad network. (Privacy Policy)
    AppNexusThis is an ad network. (Privacy Policy)
    OpenxThis is an ad network. (Privacy Policy)
    Rubicon ProjectThis is an ad network. (Privacy Policy)
    TripleLiftThis is an ad network. (Privacy Policy)
    Say MediaWe partner with Say Media to deliver ad campaigns on our sites. (Privacy Policy)
    Remarketing PixelsWe may use remarketing pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to advertise the HubPages Service to people that have visited our sites.
    Conversion Tracking PixelsWe may use conversion tracking pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to identify when an advertisement has successfully resulted in the desired action, such as signing up for the HubPages Service or publishing an article on the HubPages Service.
    Statistics
    Author Google AnalyticsThis is used to provide traffic data and reports to the authors of articles on the HubPages Service. (Privacy Policy)
    ComscoreComScore is a media measurement and analytics company providing marketing data and analytics to enterprises, media and advertising agencies, and publishers. Non-consent will result in ComScore only processing obfuscated personal data. (Privacy Policy)
    Amazon Tracking PixelSome articles display amazon products as part of the Amazon Affiliate program, this pixel provides traffic statistics for those products (Privacy Policy)
    ClickscoThis is a data management platform studying reader behavior (Privacy Policy)