In this article:

IF Not Error Google Sheets (Easiest Way in 2024)

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

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. 

Automate repetitive tasks with Lido

Save hours on repetitive and tedious work. Lido is a new spreadsheet that connects your spreadsheets, forms, PDFs, and email inbox.

Select trigger
When a cell value in Google Sheets changes
Check Google Sheets for today's date
When a new row is added to Google Sheets
On new Google Form submission
Send me a daily reminder
On new Typeform submission
When a cell value in database changes
Check database for today's date
When a new row is added in database
When a new HubSpot customer is created
Select action
Creates a one- or multiple-day calendar event with optional attendees
Adds an object to Hubspot
Create a Slack channel, and optionally add a topic or members
Makes any HTTP request
Replaces [@column_name] values in a Google Doc with the corresponding table row's values
Replaces [@column_name] values in a Google Doc with the corresponding table row's values, then export it as a PDF
Makes any HTTP request and returns the response
Inserts given array below defined values in given worksheet
Sends an email using your Google account
Sends an email using your Microsoft Outlook account
Sends a Slack message
Sends an SMS message using your Twilio account
Updates cells with given values
Updates a property of a Hubspot object
Trigger is required
Action is required
Get started