# ISNA Function in Excel

🌟 Part 1: Introduce

Definition: The ISNA function in Microsoft Excel is a powerful built-in function for “IS Not Available.” It checks whether a specific value or reference results in the #N/A (Not Available) error. The #N/A error occurs when a value is not found in a lookup operation or when a formula returns a non-existent value.

Purpose: The main purpose of the ISNA function is to handle errors and create robust formulas. Using this function, you can determine if a particular cell contains the #N/A error and take appropriate actions, such as displaying an alternative message or performing a different calculation.

Syntax & Arguments:

syntax
```=ISNA(value) ```
• `value`: This is the value or reference you want to check for the #N/A error. It can be a cell reference or a direct value.

Explain the Arguments in the Function: The `value` argument is mandatory and represents the cell or value you want to check for the #N/A error. If the provided `value` results in the #N/A error, the ISNA function will return TRUE; otherwise, it will return FALSE.

Return Value: The ISNA function returns either TRUE or FALSE.

• TRUE: If the `value` contains the #N/A error.
• FALSE: If the `value` does not contain the #N/A error.

Remarks: The ISNA function is handy when working with lookup functions like VLOOKUP, HLOOKUP, INDEX-MATCH, etc. It helps you identify missing or unavailable data, enabling you to handle such cases effectively in your Excel spreadsheets.

🌟 Part 2: Examples 🌟

1️⃣ Example using ISNA with VLOOKUP:

ABC
1
2Product CodePriceSearch Code
4456\$50
5789\$35

Explanation:

• This Example shows a table of products with their corresponding prices and product codes.
• In cell C3, we use the ISNA function to check if the VLOOKUP function (looking up the price based on the search code) results in the #N/A error.
• If the product with the search code in C3 is not found, the ISNA function returns TRUE, and the IF function displays “Product Not Found.”
• If the product is found, the ISNA function returns FALSE, and the VLOOKUP function retrieves the corresponding price.

2️⃣ Example using ISNA with INDEX-MATCH:

ABC
1
2Employee NameSalarySearch Name
4Emma\$45,000
5Sarah\$55,000

Explanation:

• This Example shows a table of employees with their corresponding salaries and names.
• In cell C3, we use the ISNA function to check if the INDEX-MATCH function (looking up the salary based on the search name) results in the #N/A error.
• If the employee with the search name in C3 is not found, the ISNA function returns TRUE, and the IF function displays “Employee Not Found.”
• If the employee is found, the ISNA function returns FALSE, and the INDEX-MATCH function retrieves the corresponding salary.

3️⃣ Examples using ISNA with SUMIF:

ABC
1
2CategoryAmountSearch Category
4Travel\$100
5Food\$30
6Travel\$150

Explanation:

• This Example shows a table of expenses with their corresponding categories and amounts.
• In cell C3, we use the ISNA function to check if the SUMIF function (calculating the total expenses based on the search category) results in the #N/A error.
• If the category in C3 is not found in the range of expense categories (A3:A6), the ISNA function returns TRUE, and the IF function displays “Category Not Found.”
• If the category is found, the ISNA function returns FALSE, and the SUMIF function calculates the total expenses for that category.

4️⃣ Example using ISNA with IF and IFERROR, nested with VLOOKUP:

ABC
1
2Product CodePriceSearch Code
3123\$25=IF(ISNA(VLOOKUP(C3, A3:B5, 2, FALSE)), “Product Not Found”, IFERROR(VLOOKUP(C3, A3:B5, 2, FALSE), “Price Unavailable”))
4456\$50
5789\$35

Explanation:

• This Example shows a table of products with their corresponding prices and product codes.
• In cell C3, we use the ISNA function to check if the VLOOKUP function (looking up the price based on the search code) results in the #N/A error.
• If the product with the search code in C3 is not found, the ISNA function returns TRUE, and the IF function displays “Product Not Found.”
• If the product is found, but the price is unavailable (results in error), the IFERROR function returns “Price Unavailable.”
• If the product is found and the price is available, the ISNA function returns FALSE, and the VLOOKUP function retrieves the corresponding price.

5️⃣ Example using ISNA with IF and IFERROR, nested with INDEX-MATCH:

ABC
1
2Employee NameSalarySearch Name
3John\$50,000=IF(ISNA(INDEX(B3:B5, MATCH(C3, A3:A5, 0))), “Employee Not Found”, IFERROR(INDEX(B3:B5, MATCH(C3, A3:A5, 0)), “Salary Unavailable”))
4Emma\$45,000
5Sarah\$55,000

Explanation:

• This Example shows a table of employees with their corresponding salaries and names.
• In cell C3, we use the ISNA function to check if the INDEX-MATCH function (looking up the salary based on the search name) results in the #N/A error.
• If the employee with the search name in C3 is not found, the ISNA function returns TRUE, and the IF function displays “Employee Not Found.”
• If the employee is found, but the salary is unavailable (results in error), the IFERROR function returns “Salary Unavailable.”
• If the employee is found and the salary is available, the ISNA function returns FALSE, and the INDEX-MATCH function retrieves the corresponding compensation.

6️⃣ Example using ISNA with IF and IFERROR, nested with SUMIF:

ABC
1
2CategoryAmountSearch Category
4Travel\$100
5Food\$30
6Travel\$150

Explanation:

• This Example shows a table of expenses with their corresponding categories and amounts.
• In cell C3, we use the ISNA function to check if the SUMIF function (calculating the total expenses based on the search category) results in the #N/A error.
• If the category in C3 is not found in the range of expense categories (A3:A6), the ISNA function returns TRUE, and the IF function displays “Category Not Found.”
• If the category is found, but the amount is unavailable (results in error), the IFERROR function returns “Amount Unavailable.”
• If the category is found and the amount is available, the ISNA function returns FALSE, and the SUMIF function calculates the total expenses for that category.

7️⃣ Example using ISNA with SUMIFS:

ABCDE
1
2Product CodeOrder #AmountSearch CodeTotal Orders
4456102\$100=C4
5789103\$35=C5
6123104\$60=C6
7789105\$45=C7
8123106\$70=C8

Explanation:

• In this example, we have a table of product orders with their corresponding amounts, order numbers, and product codes.
• In cell D3, we use the ISNA function to check if the amount in cell C3 (using the formula =C3) results in the #N/A error.
• Since cell C3 contains a valid amount, the ISNA function returns FALSE, and the SUMIFS function (in cell E3) calculates the total orders for the product with code 123 (in column A).

8️⃣ Example using ISNA with COUNTIF:

ABCD
1
2CategoryAmountSearchCount Result
4Travel\$100
5Food\$30
6Travel\$150

Explanation:

• This Example shows a table of expenses with their corresponding categories and amounts.
• In cell C3, we use the ISNA function to check if the value in B3 (using the formula =B3) results in the #N/A error.
• Since cell B3 contains a valid amount, the ISNA function returns FALSE, and the COUNTIF function (in cell D3) counts the occurrences of \$50 in the range B3:B6.

9️⃣ Example using ISNA with AVERAGEIF:

ABCD
1
2CategoryAmountSearchAverage
4Travel\$100
5Food\$30
6Travel\$150

Explanation:

• This Example shows a table of expenses with their corresponding categories and amounts.
• In cell C3, we use the ISNA function to check if the value in B3 (using the formula =B3) results in the #N/A error.
• Since cell B3 contains a valid amount, the ISNA function returns FALSE, and the AVERAGEIF function (in cell D3) calculates the average of amounts matching the category “Food.”

🔟 Example using ISNA with INDEX and ROW:

ABC
1
2ItemStockSearch Item
4Orange30
5Banana40
6Grapes20

Explanation:

• This Example shows a table of items with their corresponding stock quantities.
• In cell C3, we use the ISNA function to check if the INDEX function (looking up the item based on the row number) results in the #N/A error.
• The ROW(C3) returns 3, which is the row number of C3. Since row 3 corresponds to the item “Apple,” the ISNA function returns FALSE, and the INDEX function retrieves the object.

1️⃣1️⃣ Example using ISNA with INDIRECT:

ABC
1
2MonthSalesSearch Month
3January\$500=IF(ISNA(INDIRECT(“B” & MATCH(C3, A3:A6, 0))), “Month Not Found”, INDIRECT(“B” & MATCH(C3, A3:A6, 0)))
4February\$600
5March\$450
6April\$700

Explanation:

• This Example shows a monthly sales table with their corresponding amounts and month names.
• In cell C3, we use the ISNA function to check if the INDIRECT function (indirectly referencing the cell containing the sales amount based on the matched month) results in the #N/A error.
• The MATCH(C3, A3:A6, 0) function returns the relative row number of the month in C3, which is 3 (January). Since January’s sales amount is available, the ISNA function returns FALSE, and the INDIRECT function retrieves the sales amount.

1️⃣2️⃣ Example using ISNA with LOOKUP:

ABC
1
445F
572C

Explanation:

• This Example shows a table of test scores and corresponding letter grades.
• In cell C3, we use the ISNA function to check if the LOOKUP function (looking up the letter grade based on the search grade) results in the #N/A error.