Google Sheets ISNA Function: Syntax, Examples & Tips

value:
The value or expression that you want to check for the #N/A error. This can be a cell reference, a formula, or a constant value.

What is an ISNA?

The ISNA function in Google Sheets is a useful tool that checks whether a given value is the #N/A error. This function is particularly helpful when working with lookup functions like VLOOKUP or XLOOKUP, as it allows you to handle errors gracefully. Instead of displaying an error message, you can use ISNA to return a custom message or value when a lookup does not find a match.

In simpler terms, ISNA helps you identify when a value is not available in your data set, allowing you to manage errors effectively.

A Practical Example

Imagine you are managing a list of students and their grades stored in a Google Sheets document:

Students Table:

Grades Lookup Table:

You want to find the grade for each student in the Students Table using the Grades Lookup Table. If a student does not have a grade listed, you want to display "Grade Not Found".

ISNA Formula

To achieve this, you would use the ISNA function in combination with a lookup function like VLOOKUP as follows:

=IF(ISNA(VLOOKUP(A2, Grades!A:B, 2, FALSE)), "Grade Not Found", VLOOKUP(A2, Grades!A:B, 2, FALSE))

In this formula:

• A2 is the cell containing the StudentID from the Students Table.
• Grades!A:B is the range where the function will look for the StudentID in the Grades Lookup Table.
• 2 indicates that the function should return the value from the second column of the range.
• FALSE specifies that the function should look for an exact match.
• "Grade Not Found" is the value that will be returned if there is no match found.

Result of the Formula

When you apply the ISNA formula to the Students Table, the output would look like this:

In this case, since Charlie's StudentID (3) does not exist in the Grades Lookup Table, the formula returns "Grade Not Found".

Why Use ISNA?

ISNA is beneficial because it helps you manage errors in your spreadsheets effectively. Instead of displaying an error message that can confuse users, you can provide a clear and informative message. This enhances the usability of your spreadsheets and ensures that your data presentation is professional.

Key Takeaways:

• ISNA: A function that checks if a value is the #N/A error, useful for error handling.
• Error Management: Allows you to return custom messages instead of error codes, improving clarity.
• Common Use Cases: Ideal for situations where you are performing lookups and want to handle missing data gracefully.

ISNA is an essential function for anyone working with data in Google Sheets, providing a powerful way to enhance data integrity and user experience.

Happy spreadsheeting!