Various functions in Excel result in an error in certain situations. It’s annoying to look at them when the errors are unavoidable. In this article, you will learn how to handle such situations with the help of IFERROR function of Excel. The different types of errors that excel throws are: #NAME?, #REF!, #DIV/0!, #NULL!, #NUM!, #VALUE! And #N/A error.
All of you would be aware of the #N/A error which usually pops up when you are using VLOOKUP. Did you wonder how you could get rid of the error? Given that you know the error happens when the VLOOKUP can’t find the lookup value in the first column of the table array you have provided. Let’s see how we can use IFERROR to rectify the problem with the #N/A error.
Reasons why you’ll have to handle errors:
- In some situations, you know that Excel can throw errors but not necessarily you have to correct them, however, errors are not very presentable, Something which makes more sense would be a good replacement for these errors.
- When you use the cells that have errors in other functions like SUM or AVERAGE, the result might be an error just because one of the cells has an error
- The end user who isn’t very familiar with Excel might get confused by the errors.
A quick recap of VLOOKUP formula:
VLOOKUP formula is used to lookup for a value in a table and based on the match fetch the result from other columns in the table. The syntax for VLOOKUP is as follows:
lookup_value – The value that you want to search for.
table_array – The array of data that is to be searched for the lookup_value. Note: The Vlookup function searches in the left-most column of this array.
col_index_num – A number which specifies the column number of the supplied table_array, that you want to return a value from.
[range_lookup] – Optional. FALSE is used for exact match and TRUE is used for approximate match
When we use exact match and if the formula is not able to find the given values the VLOOKUP formula results in #N/A error. In the next section, we’ll see how we can get rid of that using an example
Example: Handling #N/A error of VLOOKUP formula using IFERROR
- Create a table as shown below. Let’s say this represents the names of different people who played a game and their final scores
- Let’s use cell E1 to input the name that we use to lookup
- If we write a formula to look up the score of the name entered in E1, we use the formula: =VLOOKUP(E1,$A$2:$B$10,2,FALSE)
- Enter the above-mentioned formula in E2 as highlighted below:
- You’ll notice that we are getting the result as 63 as we are searching for John.
- Let’s change the name to Shreyas. In this case, the formula results in an error as Shreyas is not available in the Table.
- Now, we’ll understand the usage of IFERROR. The syntax is as follows:
Value is the argument that is checked for an error. Value_if_erroris the value to return if the formula evaluates to an error.
- In the example we have, we’ll use IFERROR on the result of VLOOKUP and show an appropriate message like “Name not found” when the result of VLOOKUP is an error
- In cell E3, enter the formula: =IFERROR(VLOOKUP(E1,$A$2:$B$10,2,FALSE),”Name not found”).
- Refer to the highlighted section in the screenshot below:
- Now, instead of an error we are getting the message “Name not found”
This way IFERROR can be used to hide the known errors. To give better and more presentable results. As I’ve mentioned earlier, it can also hide #DIV/0!, #NAME?, #NULL!, #NUM!, #REF!, and #VALUE! You can download and refer to the solution file from the link below:
Did you find this useful? Why not share the love and tell your friends on the different social media platforms.
- Learn all about the new XLOOKUP Function in Excel! - September 27, 2019
- FOR EACH Loop in Excel VBA - December 15, 2018