Excel ISNA function

Summary

The Excel ISNA function is designed to check if a cell contains the #N/A error, which indicates that a value is not available. This function is particularly useful in managing lookup functions like VLOOKUP, HLOOKUP, or MATCH that might return #N/A when they fail to find a match. ISNA enables users to identify #N/A errors specifically, allowing for more refined error handling and data analysis.
Syntax
				
					=ISNA(value)
				
			
  • value: The cell reference or expression you want to test for the #N/A error
Return value
  • TRUE if the specified cell or expression results in an #N/A error
  • FALSE if it contains any other value or error type

How to use

ISNA is straightforward to use and can be directly applied to a formula or cell reference to check for the presence of an #N/A error. It’s commonly used in combination with IF to provide alternative results or actions when #N/A errors are detected, improving the clarity and functionality of your spreadsheets.

Examples

Simple ISNA
Detecting #N/A Errors in Lookup Results: To check if a VLOOKUP function results in an #N/A error.
				
					=ISNA(VLOOKUP(A1, B2:C100, 2, FALSE))

				
			
If the VLOOKUP searching for the value in A1 within B2:C100 does not find a match, resulting in an #N/A error, this formula returns TRUE, indicating the presence of the #N/A error.
ISNA with Conditional Logic
Providing Custom Messages for #N/A Errors: Using ISNA within an IF statement to handle #N/A errors gracefully.
				
					=IF(ISNA(VLOOKUP(A1, B2:C100, 2, FALSE)), "Value not found", VLOOKUP(A1, B2:C100, 2, FALSE))
				
			
This formula attempts a VLOOKUP and checks for an #N/A error using ISNA. If an #N/A error is detected, it displays “Value not found”; otherwise, it shows the result of the VLOOKUP.
ISNA in Data Cleaning
Identifying and Marking #N/A Errors for Review: To flag cells with #N/A errors for further investigation or correction.
				
					=IF(ISNA(A1), "Check data", A1)
				
			
This applies ISNA to cell A1 and flags it with “Check data” if an #N/A error is found, otherwise displaying the cell’s actual content, aiding in data validation and cleaning processes.

Additional Notes

  • ISNA is a crucial function for error handling in Excel, especially when working with data retrieval functions that might not always return a value.
  • By differentiating #N/A errors from other types of errors, ISNA allows for precise control over how these specific cases are addressed in formulas and data processing routines.

Related Functions

Excel ISERR function

The Excel ISERR function tests for any error except #N/A, returning TRUE if found, aiding in error management excluding #N/A.

Excel ISTEXT function

The Excel ISTEXT function determines if a cell contains text, returning TRUE or FALSE, essential for text data verification.

Excel ISFORMULA function

The Excel ISFORMULA function determines if a cell contains a formula, returning TRUE or FALSE, useful for formula auditing.

Excel ISREF function

The Excel ISREF function checks if a value is a reference to a cell, returning TRUE or FALSE, useful for reference validation.

Excel ISNUMBER function

The Excel ISNUMBER function verifies if a cell contains a number, returning TRUE or FALSE, key for data type validation.

Excel ISBLANK function

The Excel ISBLANK function checks if a cell is empty, returning TRUE or FALSE, ideal for conditional logic in formulas.

Content Navigation