The IFERROR Function is normally used when it is expected that your function might return different errors such as #REF, #NAME?, #DIV/0, #VALUE, #ERROR! or other error prompts in Google Sheets.
For this example, we are dividing the values in column A to the values in column B. In doing so, we encounter #DIV/0! Errors on our Google Sheet in Cells C7 and C12.
2. In the column where our formula is used, use the IFERRORFunction
The syntax of the formula is
Test_value: contains either a formula, cell reference or value that we suspect to return an error prompt. For our example, this would be the formula A2/B2.
Value_if_error: This is what you want your cell to display instead of displaying an error. You can use blanks “” or pre-set values or text displays. We will be displaying blanks for our example and use “” for this.
Following our breakdown, we will use =IFERROR(A2/B2,"") on cell C2.
3. Copy your formula down to other cells
Upon applying the IFERROR formula to other cells in our Google Sheets workbook, we can see that all cells that previously displayed an error before now only show blank cells.
Using this function helps us make our spreadsheets look cleaner and to reduce the possibility of compounding errors.
Another specific error function is available on Google Sheets and this is the IFNA function.
The #N/A error means that there is no value available for your function to return. This is usually returned for position search functions like VLOOKUP, HLOOKUP and XLOOKUP as well as various IF functions.
1. Determine if your formula is likely to return an #N/A error
For our example below, we are using the VLOOKUP Function to return the name corresponding to the number 0. However, since the number 0 is not in our list, our VLOOKUP function cannot return a value and the result is the #N/A error.
2. Use the IFNAfunction in the place of your original function
In the cell containing our VLOOKUP function, cell E2, we will use the IFNA formula.
The syntax for the Google Sheets IFNA formula is as follows:
Value_if_na: Here we will put what we want our cell to display instead of the #N/A error. Let us display the text “Not in range” so that it is easier to understand. Remember to always enclose static text values in quotation marks for formulas.
Being guided by this breakdown, we put the following formula in cell E2:
=IFNA(VLOOKUP(D2,A2:B13,2,0),"Not in range")
3. Finally, the #N/A error will no longer appear when we use our function.
Instead it will show “Not in range”, our value_if_na which is more intuitive than the #N/A error.
You can double check this by putting the number 16 in cell D2 and it would also display “Not in range”