How to avoid #N/A, #REF! in your sheet – Learn IFERROR in excel

How to avoid #N/A, #REF! in your sheet – Learn IFERROR in excel
5 (100%) 21 votes

Learn IFERROR in excel in less than 5 minutes

Time to say good bye to #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL!. errors from your excel sheet. Time to learn a new awesome function IFERROR in excel. I am 100% sure that you will not take chance to send your next VLOOKUP excel sheet to your client without this formula. IFFERROR in excel was introduced after MS Excel 2003 release. The idea behind this formula is to replace errors (such as #N/A, #VALUE! etc) with a logical text, which is informative. Such type of error makes a low quality of excel sheet. If you will use IFERROR in excel sheet then it will replace errors with a logical text and improves the quality in excel. It is easy to learn but requires some practice.

What does IFERROR function do?Connect on Linkedin

This formula returns a value you specify if a formula throws to an error (such as #N/A, #REF); otherwise, returns the result of the formula. You can use the IFERROR function to catch and handle errors in a formula. So if you apply this formula anywhere in your sheet then that particular cell will either have a values or a logical text which explains the error. It will not have any garbage value.

Related Article : This MS excel function can help you in Master Data cleansing.

Let us learn IFERROR in excel

I am sure you will learn application of this formula in less than 5 minutes. Let us learn this formula step by step.

Step 1: First we will learn the syntax of IFERROR function in MS excel. Here you go:

IFERROR(value, value_if_error)

The IFERROR function syntax has the following meaning:

  • Value    Required. The argument that is checked for an error.
  • Value_if_error    Required. The value to return if the formula evaluates to an error. The following error types are evaluated: #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL!.

 

Step 2: Now create an excel with some materials and put VLOOKUP formula in it. Please refer below screen shot and see what happens when no match is found in VLOOKUP. System will return a #N/A in the output.

VLOOKUP with NA error
Step 2: VLOOKUP with NA error

Step 3: Now apply IFERROR formula in VLOOKUP and replace error with logical text.

IFERROR in excel
Step 3: IFERROR in excel

This is how IFERROR in excel works.

Special Note: If Value or Value_if_error is an empty cell, IFERROR treats it as an empty string value (“”)

Vaibhav

Author: Vaibhav

Vaibhav is an engineer by schooling and an SAP Consultant by training with 12 years of experience. He is certified in SAP PM and SAP MM. His expertise includes SAP PM, SAP MM, SAP QM, and MS excel. His industry specialties are Chemical, Automobile, Electronics and Infrastructure. He has lots of experience in SAP Training and Consulting.

No Comments

Leave a Reply

Your email address will not be published. Required fields are marked *

Time limit is exhausted. Please reload CAPTCHA.

error: Content is protected !!