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.
Follow the steps below to use the ISNA function in Google Sheets.
Click on any cell where you want the result of the ISNA function to appear.
Type =ISNA, and you'll see Google Sheets suggests the ISNA function.
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.
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.
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:
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.
Click on the cell where you want the result (e.g., E1).
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.
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))
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."
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.
