IF Not Error Google Sheets (Easiest Way in 2023)

4 Minutes

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:

IF Not Error Using the IFERROR Function

Access the sample Google Sheet here: IFERROR Samples (Sheet 1)

1. Identify where the error is likely to occur

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.

IFERROR function on Google Sheets

2. In the column where our formula is used, use the IFERROR Function

The syntax of the formula is


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.

How to use the Google Sheets IFERROR Function

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.

Result of using the IFERROR Function

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.

IF Not Error Using 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.

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.

#N/A Error on Google Sheets

2. Use the IFNA function 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:

=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")

Using the IFNA Function in Google Sheets

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.

Google Sheets IFNA formula

You can double check this by putting the number 16 in cell D2 and it would also display “Not in range”

IFNA for VLOOKUP on Google Sheets

Tired of constantly monitoring your spreadsheets? Scale operations with Lido automations and free up time to focus on what really matters.