How to use the IFERROR function in Google Sheets

1. IFERROR Function

Figure 1.1 Types of Error 

1.1 Types of Errors

Spreadsheet formulas have rules and properties when it comes to their syntax, inputs and processes. If the required rules and properties are not satisfied, the formulas return an error code depending on the cause of the error (Fig. 1.1). Let’s look at the different types of error and their causes.

1.1.1 #DIV/0! 

Any number divided by 0 will return the error code: #DIV/0! (Fig. 1.2). A Blank cell and an empty string (“”) are treated as zero in arithmetic operations. 

Figure 1.2 #DIV/0! Error

1.1.2 #VALUE! 

The inputs of the formulas in Google Sheets have their own set of properties. One of these properties is the data type. Most of the inputs can only accept certain data types, and if that is not satisfied, the #VALUE! Error is returned. Another property of the inputs are their range of values. Some inputs can only be positive numbers, others can only accept letters and so on.

There are also functions that returns #VALUE! Error when certain conditions are not met in the process. Let’s look at some examples for this error. 

  1. Wrong Data Type

One of the common scenarios where the #VALUE! Error can occur is arithmetic operations with texts. Functions and operations in Google Sheets that involve arithmetic operations can recognize numbers that are written in text, but if the texts are letters or other special characters, the formula can’t evaluate the inputs. Let’s look at the example below. 

Input 1 is divided by Input 2, which is a letter (Fig. 1.3). The formula can only evaluate inputs that can be recognized as numbers, and Input 2 is not. Therefore, the formula returns the #VALUE! Error (C2). 

Figure 1.3 #VALUE!! Error: Arithmetic Operation with a Letter

  1. Correct Data Type but invalid Input Value

Here’s another scenario. The 3rd input of the VLOOKUP function can only accept numbers, which can also be written in text format, that are greater than 0. If this is not satisfied, the formula will throw the #VALUE! Error (Fig. 1.4). 

Figure 1.4 #VALUE!! Error: VLOOKUP Function with invalid Index Value

  1. Output

The most common scenarios here are from SEARCH and FIND functions. These functions search for a character or text  within a given text. If the functions can’t find the lookup text, then the #VALUE! Error is returned instead (Fig. 1.5). 

Figure 1.5 #VALUE!! Error: SEARCH function can’t find the lookup text

1.1.3 #REF! 

This error is related to the cell references in the formulas. There are also rules for writing references in formulas, which must be followed in order for a formula to work, but this is not the only cause that can trigger the #REF! Error. Since the references are connected to the cells, there are certain actions applied on the cells that can invalidate the references. References that are generated from formulas can also be invalid depending on the inputs. Let’s discuss each of them in the following sections. 

  1. Deleting Cells

If a cell that is referenced in a formula is deleted, the reference in the formula becomes invalid, and the formula returns the #REF! Error (Fig. 1.6). This typically happens when a row, column or a sheet is deleted. The reference in the formula is replaced with a #REF! syntax, and therefore, the formula won’t be able to perform the operation.

Figure 1.6 #REF! Error due to a Deleted Cell

  1. Out of Bounds: Relative References

Relative references are automatically adjusted relative to the position of the formula when copied or dragged in the spreadsheet. If a formula contains relative references of cells that are near the row and column headers of the spreadsheet, moving or copying the formula closer to the headers won’t work. 

If we copy the formula in C2 from Fig. 1.6 to A4, a #REF! Error is returned instead. The formula contains two relative references: A2 and B2 (Fig. 1.7). Relative to C2, A2 and B2 are 2 columns and 1 column away to the left, respectively. If we copy this formula to A4, the relative references will adjust based on the aforementioned distances and positions. Therefore, A2 and B2 will be adjusted relative to A4, but the formula that is copied to A4 won’t be able to adjust because it’s at the boundary of the spreadsheet. The adjustment will be out of bounds, and the formula will return a #REF! Error. Just like the first scenario, the invalid references are replaced with #REF!, which can’t be evaluated by the formula. 

Figure 1.7 #REF! Error due to Copying the Formula to near to the Header

  1. Out of Bounds: References generated from Functions

The first two scenarios are related to actions that are applied to the referenced cells, which affects the syntax of the input references. In this section, the syntax of the references are valid, but processing these inputs results to invalid references. The most common example is the #REF! Error from a VLOOKUP formula (Fig. 1.8). 

The range in the VLOOKUP formula has 3 columns (A to C), but the index is 4, which is beyond the number of columns for the given range. The resulting coordinates from this will be out of bounds, and therefore, the VLOOKUP formula will return the #REF! Error. 

Another example is from the OFFSET function, which returns a cell reference that is displaced from a given cell reference. The OFFSET formula below will return a #REF! Error. 

=OFFSET(A1,,-1)

The given cell reference is A1, and it will only be displaced by -1 (1 column to the left of A1). There’s no displacement in terms of row, which is the 2nd input. Therefore, the new reference will be 1 column to the left of A1, but as you know, there’s no cell to the left of A1. This is out of bounds, and therefore, the OFFSET formula will return the #REF! Error. 

Figure 1.8 #REF! Error due to Out of Bounds Range for VLOOKUP

  1. Invalid Inputs

The #REF! Error can also be triggered by not satisfying the rules and properties of the inputs. One rule is that we can’t reference the destination cell of the formula in the formula itself (Circular Dependency).  As we can see in the scenario in Fig. 1.9, C2 is the destination of the formula, but it’s also referenced in the formula. The formula won’t be able to evaluate the input and will return the #REF! Error. 

Figure 1.9 #REF! Error due to Circular Dependency

Another example is from the INDIRECT function. It takes in cell references in text format. If this property is not satisfied, the INDIRECT function will return the #REF! Error: 

=INDIRECT(A1) (#REF!)

=INDIRECT(“A1”) (Returns the value of A1)

1.1.4 #NAME? 

The #NAME? Error is related to the names of functions and named ranges and is more of a syntax error. 

  1. Function Name

If the name of the function is misspelled like the SUM function in the example below (Fig. 1.10), then Google Sheets won’t recognize the function and will return the #NAME? Error. 

Figure 1.10 #NAME? Error due to Typo in the Function Name

  1. Named Range

Another cause of this error are named ranges. If a named range is misspelled in a formula or if a named range that doesn’t exist is used in a formula, then the #NAME? Error will be returned instead. 

Most of the time, we can have typographical errors when typing the references for ranges of cells such as forgetting to add the colon in between the first cell and the last cell in the range (Fig. 1.11). The formula will treat this syntax as a named range, which doesn’t exist. Therefore, it will throw the #NAME? Error. 

Figure 1.11 #NAME? Error due to Typo in the reference syntax

1.1.5 #NUM! 

This error can be triggered by invalid numerical input or output. Some formulas will return #VALUE! Error If the inputs have invalid values for their inputs while other formulas will return the #NUM! Error for the same cause, but unlike the #VALUE! Error, which can be triggered by wrong data type and input values, the #NUM! Error is triggered by incorrect values and is more related to functions that perform calculations. Let’s look at common examples that can trigger the #NUM! Error. 

  1. Invalid Input

The most common example for this scenario is using a negative number in the SQRT function. The SQRT function can’t evaluate the square root of negative numbers, and therefore, it will throw the #NUM! Error if the input inside the function is negative (Fig. 1.12). 

Figure 1.12 #NUM! Error due to negative number in SQRT

  1. Output Value is too Large

The output numerical value in Google Sheets has an upper limit (i.e., 1.79769E+308), and if a calculation generates a value greater than the aforementioned limit, the #NUM! Error will be returned instead. 

1.1.6 #N/A

This error just means not available. This type of error is typically common in lookup functions such as VLOOKUP and MATCH. These functions search through a matching process, and If the conditions are not satisfied, which means there’s no match, then the #N/A error is returned. 

The MATCH function matches a lookup value on a given list. It returns its coordinate if it finds the match. Otherwise, it returns the #N/A if it can’t find the lookup value (Fig. 1.13). 

Figure 1.13 #N/A Error: Lookup Formulas

Another example is from the IFS function. It’s an IF function for multiple criteria. In the given example below, a %discount is automatically determined based on the amount given in Column C(Fig. 1.14). We listed the criteria for each of the discounts in the IFS function that will determine the correct discount. The IFS function will check the first criterion, which is C6<1000. If this is true, it will return 5%. Otherwise, it proceeds to the next criterion, which is C6<2000, and the process progresses this way. Now, if we look at the last entry in the Amount Column, which is 3,000, it doesn’t satisfy any of the conditions given in the IFS function. Therefore, the IFS function will return the #N/A error instead of a discount because it can’t find a condition where the value is valid. 

Figure 1.14 #N/A Error: IFS function

1.1.7 #ERROR

This error is a syntax error that is not related to the name of the function or named ranges. This can be caused by forgotten commas (Fig. 1.15), extra space and quotations and so on. It’s a general error for syntax errors in formulas. 

Figure 1.15 #ERROR: No comma or colon between the references

1.1.8 Errors in Nested Formulas

There’s no priority on what error will be returned first based on the type of error. It depends on what error is triggered first. In the case where there are a lot of formulas that are nested, the first formula that triggers the error will return the error code corresponding to that particular error. The process will stop on that formula and will not proceed to the next formula. 

The formula in the scenario below contains three cases that can trigger errors. The first one is that the search_key in E2 is not present in the Code Column (Fig. 1.16). The 2nd is that the index of the VLOOKUP function is out of bounds. The last is that the result of the VLOOKUP function will be divided by 0. As you can see, the error code that is returned by the formula is from the 2nd case.  The VLOOKUP function is evaluated first, and therefore, the error will be triggered by it first. The surprising thing here is that the index is the one that caused the error and not the matching process. This means that the VLOOKUP function checks first if it’s inputs are following their rules and properties before starting the process of lookup. Another possible conclusion is that the error will halt the process and won’t proceed to the next part of the formula. 

Figure 1.16 Errors from 3 different sources

1.2 Error-Handling

Errors are always part of spreadsheet formulas. In most scenarios, we expect that the formulas will encounter errors due to the changes in the input data. There’s always a possibility that there will be changes in the data that will lead to errors, and we must be able to take this into consideration. Therefore, we need to assist the formulas with error-handling techniques that will handle the errors if they occur. We’ve already discussed the types of errors and their causes. Most of the time, the errors that we need to look out for are the errors that are not syntax-related errors because we always make sure that the syntaxes are working. The following errors that we need to consider are: #DIV/0!, #VALUE!, #REF!, #NUM! And #N/A. These errors can be triggered by the inputs and the processes of the functions, but it all boils down to the inputs. We need to construct the formulas to handle different input scenarios. We are not trying to prevent the errors. Instead, we are trying to replace or bypass the error so that the process can proceed or provide a way to warn us in a way that is more familiar to us than just error codes. 

There are a few error-handling techniques in spreadsheets. One common way of doing this is by using the IFERROR function. 

1.3 What does the IFERROR do in Google Sheets?

The IFERROR function checks if the input is an error. If it’s true, it will return a value that we can set. Otherwise, if there’s no error, it will return the input value itself. 

1.4 IFERROR Syntax

Below is the syntax for IFERROR:

The IFERROR function has two inputs: value and value_iferror. The value is the input that will be checked for errors. The value_if_error is the value that will be returned by the function if the value is an error. The latter input is an optional input. This means that we can omit it from the formula, and it will take its default value, which is blank. It’s important to note here that blank is different from an empty string (“”). Blank means blank or empty cell. Let’s take a look at a basic example (Fig. 1.17)  to help us build an intuition on how the formula works. 

#N/A Error is always expected in VLOOKUP formulas. There are times where the function won’t be able to find a match in the first column, and therefore, we need to use error-handling to take care of this. In the example below, the search_key is not present in the Code Column. Therefore, we expect the VLOOKUP function to return the #N/A Error, but it’s not pleasing to see an error as an output. The IFERROR is used here to replace the #N/A Error with the “No Match!” statement. 

The VLOOKUP function is enclosed by the IFERROR function. The result of the VLOOKUP function will be the value input for the IFERROR function. If the result of the VLOOKUP function is an error, then the IFERROR function will return the statement that we’ve set in the value_if_error. If the result of the VLOOKUP function is not an error, then the IFERROR function will return this value instead. 

Figure 1.17 Basic example of IFERROR Process

  1. Breaking Down the Formula

Figure 2.1 IFERROR Function

In this section, we’ll talk about the rules and criteria for each IFERROR input, how to write them, how IFERROR processes these inputs and how do the inputs affect the output. We’ll build an intuition to make it easier for us to visualize and create an IFERROR formula for your problems.

2.1 IFERROR Input/Arguments

2.1.1 Value

Here are the important rules that you need to consider for the value:

2.1.1.1 Syntax and Location of the Value

In Google Sheets, the value can be written or coded in four different ways depending on the scenario:

  1. Reference to a cell: 

Figure 2.2 Value is Referenced from F2

Typically, our data set is stored in a sheet, and we want to be able to use this set of data in our formulas. In order to do that, we write the references of the cells where the data set is located. In the formula above, the reference F2 means that the value is in column F, row 2 (Fig. 2.2). The reference defines the address of the cell, and the formula uses the reference to access the value within the referenced cell. 

The main advantage of referencing the cells is that the formula becomes dynamic. We can change the values within the referenced cells, and the formula will update automatically in response to the changes. 

We can write the reference in the formula or highlight the cell. If we perform the latter, the spreadsheet automatically writes the references for us (Fig. 2.3), so we don’t have to write the references manually.

Figure 2.3 Referencing cells by highlighting them

Also, it’s important to know that we can refer to the cells from any sheet. This means that the cells can be:

  1. in the same sheet with the function
  2. in  another sheet within the same workbook
  3. Or in another sheet in another workbook (using ImportRange function)
  1. A Constant value:

Although this usage is not common, the value can also be hard-coded as constant: (The formula below will return a blank.)

=IFERROR(#N/A,)

  1. Value generated from another Function

This is the most common way of inputting a value in the IFERROR function. We always enclosed formulas with the IFERROR function to handle any error within a formula. Please see Fig. 1.17. 

  1. Array and Range of Cells

The arrays can be hard-coded or generated from a function. The latter is more useful than the former, and therefore, we’ll tackle the latter here. For now, we can think of an array as a list of values that are not located in any sheet. The example below is from Fig. 1.14. Instead of writing  a formula for each of the cells in the Discount Column, we combine the references into one and use one array formula in D2 (Fig. 2.4). The IFS function will return an array with values consisting of discounts and the #N/A Error (if there’s an amount that doesn’t satisfy the conditions). The IFERROR function will check each of the values in the array for errors. If there’s an error, that error will be replaced by the “No Discount” statement. 

The IFS function doesn’t normally accept arrays as inputs. We need to use the ArrayFormula function in order for this to work correctly. We can write the ArrayFormula function and put the IFERROR inside, or once you’re done writing the IFERROR function, you can just press CTRL+SHIFT+ENTER, and Google Sheets will enclosed the IFERROR function with the ArrayFormula function. 

Figure 2.4 Value is an array generated from the IFS function

It’s important to note here that the IFERROR function normally accepts an array or a range of cells as input (Fig. 2.5). We don’t need to use the ArrayFormula function except if the nested formulas inside the IFERROR function require the ArrayFormula function.

Figure 2.5 Value is a range of cells (A2:A8)

2.1.1.2 Dimension and Size of Value

In spreadsheets, the dimension is related to rows and the columns. If our data is written in one column but multiple rows or one row but multiple columns, the dimension of our data is one-dimensional (1D). If our data is in a table with multiple rows and columns, then the dimension is two dimensional (2D). 

The rows and columns serve as coordinates of the cells within the spreadsheet. For 1D data, we only need to specify one coordinate (row or column) to know where the value is located. For 2D, we need to define the row and column to locate the value. 

The size of the data is related to the number of rows and columns, which also translate to the number of entries. 

The value can be 1D or 2D:

  1. One-Dimensional (1D):
    1. Horizontal List: (Fig. 2.6)

Figure 2.6 1D Value: one row, multiple columns

  1. Vertical List: (Fig. 2.5)
  1. Two-Dimensional (2D): (Fig. 2.7)

Figure 2.7 2D Value:Multiple Rows and Columns

2.1.1.3 Data Type of Value

The value input of the IFERROR function can be any data type. 

2.1.2 Value_If_Error

The value_if_error is an optional input with a default value of blank. We can omit this in the formula if we want to return blank instead of the error code. In terms of its properties, all of the rules and properties of the value is also applicable to the value_if_error. 

2.2 The IFERROR Process

2.2.1 How does the IFERROR work?

We already had a glimpse of how it works from section 1.4, and in this section we’ll look at more detailed examples of how the IFERROR processes its inputs.

2.2.1.1 IFERROR (Basic)

We don’t need to write any logical operators for the error or separate the return value if the condition is false. Just write the value that we want to test for an error and write the return value if the condition is true. The IFERROR just checks the value if it’s equal to an error, which can be any type. If the condition is satisfied, then the value_if_error is returned. If the condition is not satisfied, then the value is returned. Another important point is that we can’t select what error to catch. The IFERROR can’t distinguish the type of errors. As long as it’s an error, it will satisfy the condition. 

2.2.1.2 IFERROR (Array and Range of Cells)

IFERROR can handle an array and a range of cells as inputs. Normally, we don’t need the ArrayFormula function to evaluate a list unless there’s a nested function that requires it. Let’s look back at the example from Fig. 2.4, where the IFS function generates a list. Each of the values generated from the IFS function will be checked for errors. This checking is represented by Column F (Fig. 2.8). The cells in the Condition Column correspond to the values in the array from the IFS function. If there’s a value that is an error, then the condition is satisfied, which is represented by the TRUE value in Column F, and the value_if_error, which is “No Discount,” is returned. Otherwise, the discount is returned (Discount Column). 

Figure 2.8 Value: Array from the IFS function

It’s also important to note that the value_if_error can also be an array or a range of cells, but we won’t tackle it here since it doesn’t have any use in most scenarios. In the case where you need to use an array or a range of cells for the value_if_error, apply the array operations to predict the dimension, size and arrangement of the output data. 

2.3 IFERROR Output

2.3.1 Dimension and Size of the Output

The dimension, size and output of the IFERROR function will be dependent on the same properties of its two inputs. In general the value is the input that is most likely to be an array or a range of cells while the value_if_error is a single value. Therefore, the output will be dependent on the value. 

For array results, just make sure that the destination cells of the outputs are vacant cells. If there are values that are occupying the cells where the results should be displayed, then an error will be returned instead of the results (Fig. 2.9). 

Figure 2.9 The IFERROR function returns a #REF error due to D3 blocking the results

2.3.2 Data Type of the Output

The data type of the output will be the same as with the data type of the value_if_error. 

  1. Applications of IFERROR

In this section, we’ll tackle how to formulate your own IFERROR for different scenarios. Some complicated tasks will require some tricks, but basically it all boils down to the rules of the IFERROR function. We just need to follow the rules, and we’ll be able to create the IFERROR formula that we want.

3.1 How to formulate a IFERROR 

  1. First we need to identify the inputs for our IFERROR:
  1. Value: It’s better to start determining the location of the Value. Is it a formula located in a cell or range of cells? Or do we need to write a formula inside the IFERROR function as its value? If there’s a nested formula, then you need to consider that formula first. 
  1. Value_If_Error: Next, we need to know the value that we want to return if there’s an error. 
  1. Preprocessing of Raw Data

Step 1 helps us identify if our data are suited as inputs for the IFERROR function.  Most of the time, we don’t have the control on the arrangement and values of our raw data. With this step, we can carefully follow the rules for each of the inputs and see if we need to take some preprocessing steps before we can apply the inputs to the function. The IFERROR function is a simple function. There’s not much complexity when it comes to its input since the main objective of the formula is to handle errors. 

  1. Setting up the IFERROR Formula:

Step 1 and Step 2 will give us an idea how to arrange the inputs of the functions and the format of the resulting output. It’s always better to visualize the inputs when constructing the formula. This will help us predict the dimension, size and arrangement of the results. Just remember that if the output is an array, we need to make sure that there are vacant cells for them to fill up. Otherwise, an error will be returned instead of the output. 

  1. Steps to write the formula:
  1.  In a cell where you want to write the formula, write =IFERROR(

As you write the formula, Google Sheets will display the formulas that match what you’re typing (Fig. 3.1). You can then select from the options that are displayed by pressing the TAB key,  or just complete the formula by writing it manually. 

Figure 3.1 Writing the IFERROR function

  1. Next, after the open parenthesis (, write the value or highlight the cell reference if the value is stored in a cell. If you highlight the cell, the spreadsheet will automatically write the reference for you. In this scenario, the value is from a VLOOKUP formula (Fig. 3.2).

Figure 3.2 VLOOKUP with IFERROR

  1. Write a comma to separate the first input, and then, write the value_if_error. If it’s a text, enclose it with quotation marks. If numbers, just write the number and so on. 

Figure 3.3 Value_If_Error

  1. We can write the close parenthesis or just hit ENTER, which will automatically complete the formula for us. 

3.2 How to do IFERROR with Arithmetic Operations

Some functions exclude texts in their calculations (e.g., SUM), but most formulas that involve arithmetic operations must be considered for error-handling. 

  1. Arithmetic Operations with Strings

One of the scenarios is when the input is a string such as letters. There are times where we want to substitute 0 for letters if we want to ignore it in our formulas, and we can do it using the IFERROR function (Fig. 3.4).  

Figure 3.4 Arithmetic Operations with Strings

  1. Arithmetic Operations with Division

If your calculations involve division, then you need to consider if the divisor (denominator) is 0. The common examples are percentage calculations and averages. The task in the example below is to calculate the average of the numbers that are greater than 5 in the given data set (Fig. 3.5). If you look at the data in Column E, there are no numbers that are greater than 5. Therefore, the AVERAGEIF function here will return a #DIV/0! Error. The goal here is to return 0 if there’s nothing to average. We enclose it with the IFERROR function and set the value_if_error as 0. 

Figure 3.5 Arithmetic Operations with Division

3.3 How to do IFERROR with Lookup Functions

Lookup formulas such as VLOOKUP, INDEX, MATCH, INDEX-MATCH, SEARCH, FIND, etc. consist of searching, finding or matching lookup values on a given data. If the lookup value is not found, these formulas will return an error. We take this into consideration, and use the IFERROR function to replace the error code returned by these formulas with the values that we want to see in our output data. 

The combination of INDEX and MATCH functions is the best alternative to the VLOOKUP function. The VLOOKUP function may be simpler but it’s slower. The INDEX-MATCH formula provides a relatively faster processing time in a larger database.

In the example below, the MATCH function will search for the lookup value given in E2 from the Code Column (Fig. 3.6). Once it finds the lookup value, it will return its coordinate, which is the row coordinate in this case. This coordinate will be used by the INDEX function to return the value from C2:C6 using the row coordinate from the MATCH function. The error here will come from the MATCH function. If it doesn’t find the lookup value, then the MATCH function will return the #N/A Error. 

Figure 3.6 IFERROR with INDEX-MATCH

3.4 How to do IFERROR with Nested Formulas

We can write a nested formula as the value or create a nested formula with the IFERROR function. If we do latter, we just need to write a function or a formula as the value_if_error. The main question here is where do you place the IFERROR function? The answer is we can enclose the whole nested formula with the IFERROR function, so that it can cover all of the parts that may cause errors. We can also be specific if we are sure that there’s only specific parts in the formula that can throw errors. 

  1. Nested Formula in Value

The task in the scenario below is to label the color of each of the data using the listed colors in Column A (Fig. 3.7). If an entry has a color that is not part of the category, then the “Not in Category” statement will be returned instead of the color. The formula that will  extract the color from the category is given by the INDEX-LARGE-IF-ISERROR-ROW-SEARCH formula, which is also an array formula. It’s a bit complicated, so we won’t delve in too much on this. This nested formula categorizes the data with the right color. The errors can be triggered in different parts of the nested formula. We enclose it with the IFERROR function. Therefore, no matter where the error will occur, the IFERROR function will catch it. 

Figure 3.7 IFERROR with Nested Formula in Value

  1. Assigning a Function to Value_If_Error

We can also create a nested IFERROR formula by using the value_if_error. In the scenario below, if the given code is not found in the Code Column, we need to sum the amount of the codes that have the same letter code as the given in E2 (Fig. 3.8). In this case, since YY-1005 is not in the Code Column, the amount will be the sum of all the codes with letter code: YY. We use the SUMIF function as the value_if_error for the IFERROR function. The LEFT function will extract the letter code from E2, and the wildcard (*) is for the partial match. If the VLOOKUP function returns an error, then the IFERROR function will execute the SUMIF function. 

Figure 3.8 Nested IFERROR

3.5 Error-Handling Alternatives

There are other methods in catching or handling errors in Google Sheets. We’ll discuss some of these methods in this section. 

  1. ISERROR

The ISERROR function only requires one input. It checks the input for errors (any type), and if it’s an error, then the ISERROR will return TRUE (Fig. 3.9). Otherwise, it will return FALSE if there’s no error. It basically returns boolean values that will tell us if there’s an error, but it won’t help us in replacing the returned error code. In order to do that, we need to combine it with the IF function just like the formula in Fig. 3.7. 

Figure 3.9 ISERROR

  1. IFNA (and ISNA)

The IFNA function is similar to the IFERROR function. They have the same number of inputs, and both formulas work the same way. The only difference is that the IFNA function is a specific function for the  #N/A Error (Fig. 3.10). It only catches the #N/A error while the IFERROR function can catch all types of errors. It’s the same with the ISNA vs. ISERROR functions. 

Figure 3.10 IFNA

  1. IF and IFS

We can also use the IF and IFS functions to prevent any errors instead of waiting for the error. In the example below, all of the possible input values that can cause errors are listed as criteria in the IFS function, and each of the scenarios has a corresponding error message, so that it will be easier for the user to correct the input (Fig. 3.11). The first three criteria in the IFS are for catching the inputs that can cause errors, and if all of the first three are false, which means that the inputs have satisfied the criteria, then the last criterion, which is set to TRUE, will execute the formula A2/B2.

Figure 3.11 IFS for preventing Errors