ISNA function

ISNA detects whether a value is the #N/A error, typically returned when a lookup function fails to find a match.

=ISNA(value)

Generate a ISNA formula

Describe what you need. The generator will reach for ISNA where ISNA is the right tool, and tell you when it is not.

How to get a better answer
  • Name your columns by letter and by header: "column F (Net Value)" beats "the amount column".
  • State every condition, including the negatives — "not cancelled" changes the formula's shape.
  • Say where the data starts if it is not row 1, and whether it will grow.
  • Check the settings above match your spreadsheet: the wrong argument separator is a syntax error on your machine.

Arguments

How ISNA reads its arguments
valuerequiredISNA →
ArgumentRequiredDescription
valueRequiredAny value, cell reference, formula, or range; ISNA tests if it equals #N/A error, ignoring data type or content.

Returns

A boolean value: TRUE if the value is #N/A, FALSE for any other value.

Availability

Excel: All · Google Sheets: Supported

Worked examples

1. Check if a vehicle lookup succeeded

VehicleOdometerService DateCostGarage
Toyota Camry450002024-01-15150Downtown Service Center
Honda Civic320002024-02-20200Metro Repairs
Ford F-150780002023-12-10450Truck Specialist
Toyota Camry475002024-03-05180Downtown Service Center
BMW 3-Series550002024-01-25300Premium Auto
Hyundai Elantra280002024-02-28120Budget Auto
=ISNA(VLOOKUP("Nissan Altima",A2:E7,4,FALSE))

Result: TRUE

VLOOKUP searches for "Nissan Altima" in the vehicle column but cannot find it in the fleet maintenance log. When VLOOKUP fails to find a match, it returns #N/A. ISNA detects this #N/A error and returns TRUE, confirming the lookup failed.

2. Detect missing service cost in INDEX-MATCH lookup

VehicleOdometerService DateCostGarage
Toyota Camry450002024-01-15150Downtown Service Center
Honda Civic320002024-02-20200Metro Repairs
Ford F-150780002023-12-10450Truck Specialist
Toyota Camry475002024-03-05180Downtown Service Center
BMW 3-Series550002024-01-25300Premium Auto
Hyundai Elantra280002024-02-28120Budget Auto
=ISNA(INDEX(E2:E7,MATCH("Honda Civic",A2:A7,0)))

Result: FALSE

MATCH finds "Honda Civic" at position 2 in the vehicle list, so INDEX retrieves the corresponding cost value of 200. Since 200 is a valid number (not #N/A), ISNA returns FALSE. The lookup succeeded and found the service cost.

3. Handle missing garage information in conditional logic

VehicleOdometerService DateCostGarage
Toyota Camry450002024-01-15150Downtown Service Center
Honda Civic320002024-02-20200Metro Repairs
Ford F-150780002023-12-10450Truck Specialist
Toyota Camry475002024-03-05180Downtown Service Center
BMW 3-Series550002024-01-25300Premium Auto
Hyundai Elantra280002024-02-28120Budget Auto
=IF(ISNA(VLOOKUP("Ford Mustang",A2:E7,5,FALSE)),"Garage not found","Garage: "&VLOOKUP("Ford Mustang",A2:E7,5,FALSE))

Result: Garage not found

VLOOKUP cannot find "Ford Mustang" in the fleet maintenance log, so it returns #N/A. ISNA detects this #N/A and the IF function returns the text "Garage not found" instead of attempting to concatenate with the error value. This prevents #N/A from appearing in the cell.

Common errors

Which ISNA error are you seeing?
ISNA returned an error#REF!
Verify that all referenced cells still exist in the spreadsheet. Use defined names or absolute references ($A$1) instead of relative ones to prevent reference breaks when moving or deleting data.
#NAME?
Check that ISNA is spelled correctly and confirm your Excel or Sheets version supports it. All modern versions include ISNA; if you see #NAME?, verify the spelling in your formula bar.
#NULL!
Use proper range notation like A1:A10 or A1:A10, B1:B10 with commas. Ensure ranges are properly delimited and separated with appropriate operators or semicolons depending on your locale settings.
ErrorWhy it happensHow to fix it
#REF!The value argument references a cell or range that has been deleted, moved, or is otherwise invalid.Verify that all referenced cells still exist in the spreadsheet. Use defined names or absolute references ($A$1) instead of relative ones to prevent reference breaks when moving or deleting data.
#NAME?The ISNA function name is misspelled or not recognized by your spreadsheet application (occurs in very old versions that lack this function).Check that ISNA is spelled correctly and confirm your Excel or Sheets version supports it. All modern versions include ISNA; if you see #NAME?, verify the spelling in your formula bar.
#NULL!Incorrect range syntax in the value argument, such as using spaces between ranges without an intersection operator, or improper range separators.Use proper range notation like A1:A10 or A1:A10, B1:B10 with commas. Ensure ranges are properly delimited and separated with appropriate operators or semicolons depending on your locale settings.

Tips and when to use something else

  • Use IFNA() instead if you want to replace #N/A errors with a default value in one step—it's cleaner than wrapping ISNA with IF.
  • ISNA only detects #N/A errors; if you need to catch all error types (#VALUE!, #DIV/0!, etc.), use ISERROR() or IFERROR() instead.
  • Combine ISNA with IF() to take different actions when a lookup succeeds or fails, such as showing a message or alternative value.
  • For lookup functions in modern Excel or Sheets, consider XLOOKUP or FILTER as alternatives that don't return #N/A errors for missing values.

Related functions

IFNAIFNA returns the result of a formula unless it evaluates to #N/A, in which case it returns a specified alternate value.ISERRORISERROR returns TRUE if a value is an error like #N/A or #REF!, FALSE otherwise, enabling error detection and handling.IFERRORIFERROR returns the result of a formula unless that formula generates an error, in which case it returns a user-specified alternative value.VLOOKUPVLOOKUP searches the first column of a table and returns a value from a specified column in the same row, optionally using exact or approximate matching.INDEXINDEX returns the value or array of values from a specified position within a given range, based on row and optional column numbers.

Frequently asked questions

How is ISNA different from ISERROR?
ISNA specifically tests for #N/A errors, while ISERROR detects any error type (#VALUE!, #DIV/0!, #REF!, etc.). Use ISNA when you only need to handle missing lookup values; use ISERROR for broader error handling across all error types.
Can ISNA be used with data that doesn't come from lookup functions?
Yes, ISNA can test any value. If a cell contains #N/A from any source—whether a failed lookup, a formula error, or manually entered—ISNA returns TRUE. Most commonly, #N/A arises from VLOOKUP, XLOOKUP, or MATCH when no match is found.
What's the difference between ISNA and IFNA?
ISNA only detects #N/A and returns TRUE or FALSE; IFNA detects #N/A and replaces it with a fallback value in a single formula. Use ISNA when you just want to test for errors; use IFNA when you want to replace #N/A with cleaner output.
Why should I use ISNA instead of just checking if a cell is empty?
ISBLANK tests for empty cells (nothing in them), while ISNA tests for the #N/A error value (an error state). These are different: a lookup that fails produces #N/A, not an empty cell. Use ISNA to specifically catch lookup failures and distinguish them from blank cells.

Need a different formula?

The full generator is not scoped to one function — describe any spreadsheet problem and it will pick.

Open the formula generator

Reviewed 2026-09-17