In this article:

Google Sheets ISNA Function: The Ultimate Guide for 2024

May 8, 2024

Google Sheets ISNA Function Explained

ISNA is a function used to check if a cell contains the #N/A error, which often arises when data is not found, especially in functions like VLOOKUP, HLOOKUP, or MATCH. The ISNA function returns TRUE if the value is the #N/A error and FALSE otherwise.

Here's how you can use the ISNA function in Google Sheets:

=ISNA(value)

value: The value or expression to be checked for the #N/A error.

How to Use the ISNA Function in Google Sheets

Follow the steps below to use the ISNA function in Google Sheets.

Step 1: Choose a Cell

Click on any cell where you want the result of the ISNA function to appear.

isna google sheets

Step 2: Type the ISNA Function

Type =ISNA, and you'll see Google Sheets suggests the ISNA function.

isna function google sheets

Step 3: Enter the Cell Reference or Expression

After =ISNA, specify the cell reference or expression you want to check. For example, A1 or VLOOKUP(...). After specifying the cell or expression, close the parenthesis.

isna function in google sheets

Step 4: Press Enter

Press the Enter key, and you will see the result. It will show TRUE if the cell or expression results in #N/A, and FALSE otherwise.

isna function google sheets example

Example

Here's a simple example of how to use ISNA with VLOOKUP:

Suppose you have a simple product inventory in Google Sheets with the following data:

google sheets isna

Now, you want to look up the price of a product by its name and handle the case where the product name might not exist in the list.

Let's say you want to look up the price of "Banana" and handle the case where the product might not be in the list.

Step 1: Select a Cell

Click on the cell where you want the result (e.g., E1).

Step 2: Run VLOOKUP

You start with a VLOOKUP function to find "Banana" in the range B1:C3 and return the corresponding price from the 3rd column.

=VLOOKUP("Banana", B1:C3, 2, FALSE)

This would normally return 0.75 as the Banana's price. 

google sheets isna function

Step 3: Use ISNA and IF for Error Handling

Now, wrap the VLOOKUP with ISNA to check if the result is #N/A. Combine with IF to display a friendly message if not found.

=IF(ISNA(VLOOKUP("Banana", B1:C3, 2, FALSE)), "Product not found", VLOOKUP("Banana", B1:C3, 2, FALSE))

how to use isna in google sheets
isna in google sheets

This formula will return 0.75 if "Banana" is found, and "Product not found" if it's not in the list. For instance, if you change "Banana" to a product name that's not in the list (e.g., "Durian"), cell E1 will show "Product not found."

isna google sheets function

We hope that this article has helped you and given you a better understanding of the Google Sheets ISNA function. If you enjoyed this article, you might also like our articles on how to perform iterative calculation in Google Sheets and how to calculate age in Google Sheets.

Get Google Sheets productivity and automation tips delivered straight to your inbox
Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.
We'll email you 1-3 times a week — and never share your information.

Work less, automate more!

Use Lido to connect your spreadsheets to email, Slack, calendars, and more to automate data transfers and eliminate manual copying and pasting. View all use cases ->