In this article we will show how to use the IF Not Error in google sheets by using the IFERROR and IFNA functions. Simply follow the steps below:
Access the sample Google Sheet here: IFERROR Samples (Sheet 1)
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.
The syntax of the formula is
=IFERROR(test_value,[value_if_error])
Formula Breakdown:
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.
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.
Access the sample Google Sheet here: IFERROR Samples (Sheet 2)
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.
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.
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:
=IFNA(test_value, value_if_na)
Formula Breakdown:
Test_value: the formula or cell reference which is likely to return an #N/A error prompt. For our example, this would be our VLOOKUP formula. You can read more about the VLOOKUP function here.
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")
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”
If you enjoyed this article, you might also like our article on how to set up the Google Sheets IF Not Equal function or our article on how to use the Google Sheets Unique IF function.
If you want to learn how to email csv to Google Sheets, we also suggest checking out our detailed guide.
Use our loan portfolio deadline software to easily track deadlines from your spreadsheet in just a few clicks.
