How ISNA works in Excel? Best ISNA examples

How ISNA works in Excel? Best ISNA examples

If you’ve ever worked with Excel and dealt with errors in formulas, you know how frustrating they can be. Fortunately, Excel provides functions to handle errors gracefully. One such function is ISNA. In this article, I’ll explain how ISNA works in Excel, when to use it, and provide the best ISNA examples.

What is the ISNA Function in Excel?

The ISNA function in Excel is a logical function that checks whether a formula returns the #N/A error. If the value passed to the function is #N/A, it returns TRUE. Otherwise, it returns FALSE. This is particularly useful when working with functions like VLOOKUP or MATCH, which may return #N/A if no match is found.

Syntax of the ISNA Function

The syntax of ISNA is quite simple:

=ISNA(value)
  • value – This is the argument that Excel will check for an #N/A error. It can be a cell reference or a formula.

When Should You Use ISNA?

The ISNA function is useful in scenarios where error handling is needed. Some common cases include:

  • Checking the result of a VLOOKUP function to see if the value exists.
  • Using ISNA with IF to display custom error messages.
  • Handling errors in large datasets where #N/A values may be present.

Best ISNA Examples in Excel

Example 1: Handling Errors in VLOOKUP

Let’s say we have a dataset in which we want to look up a product price based on its name. However, if the product is not found, VLOOKUP returns #N/A. We can use ISNA to handle this error.

Consider the following table:

Product Name Price
Apple $2
Banana $1

Now, if we try to look up the price of “Orange”, we can use ISNA for error handling:

=IF(ISNA(VLOOKUP("Orange", A2:B3, 2, FALSE)), "Product not found", VLOOKUP("Orange", A2:B3, 2, FALSE))

This formula checks if VLOOKUP returns #N/A and displays “Product not found” instead of an error.

Example 2: Using ISNA with MATCH

The MATCH function returns #N/A if a value is not found in a range. We can use ISNA to identify missing values:

=IF(ISNA(MATCH("Mango", A2:A3, 0)), "Not in the list", "Found")

This formula tells us whether “Mango” exists in column A.

Example 3: Highlighting Errors with Conditional Formatting

We can use ISNA in conditional formatting to highlight cells with #N/A values.

  1. Select the range you want to apply conditional formatting to.
  2. Go to Home > Conditional Formatting > New Rule.
  3. Select “Use a formula to determine which cells to format”.
  4. Enter the formula:
=ISNA(A2)

Now, any cell containing #N/A will be highlighted.

ISNA vs. Other Error Handling Functions

Excel provides other functions for handling errors. Here’s a comparison:

Function Description
ISNA Returns TRUE for #N/A, FALSE otherwise.
ISERROR Returns TRUE for any error, not just #N/A.
IFERROR Returns a specified value if an error occurs.

Final Thoughts

The ISNA function in Excel is a powerful tool for dealing specifically with #N/A errors. It is especially useful in combination with VLOOKUP and MATCH, allowing us to create more user-friendly and informative spreadsheets. Whether you’re a beginner or an advanced Excel user, learning how to use ISNA can help make your formulas more robust and professional.

 

Other interesting article:

How ISERROR works in Excel? Best ISERROR examples