🌟 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
valuecontains the #N/A error. - FALSE: If the
valuedoes 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:
| A | B | C | |
|---|---|---|---|
| 1 | |||
| 2 | Product Code | Price | Search Code |
| 3 | 123 | $25 | =IF(ISNA(VLOOKUP(C3, A3:B5, 2, FALSE)), “Product Not Found”, VLOOKUP(C3, A3:B5, 2, FALSE)) |
| 4 | 456 | $50 | |
| 5 | 789 | $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:
| A | B | C | |
|---|---|---|---|
| 1 | |||
| 2 | Employee Name | Salary | Search Name |
| 3 | John | $50,000 | =IF(ISNA(INDEX(B3:B5, MATCH(C3, A3:A5, 0))), “Employee Not Found”, INDEX(B3:B5, MATCH(C3, A3:A5, 0))) |
| 4 | Emma | $45,000 | |
| 5 | Sarah | $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:
| A | B | C | |
|---|---|---|---|
| 1 | |||
| 2 | Category | Amount | Search Category |
| 3 | Food | $50 | =IF(ISNA(SUMIF(A3:A6, C3, B3:B6)), “Category Not Found”, SUMIF(A3:A6, C3, B3:B6)) |
| 4 | Travel | $100 | |
| 5 | Food | $30 | |
| 6 | Travel | $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:
| A | B | C | |
|---|---|---|---|
| 1 | |||
| 2 | Product Code | Price | Search Code |
| 3 | 123 | $25 | =IF(ISNA(VLOOKUP(C3, A3:B5, 2, FALSE)), “Product Not Found”, IFERROR(VLOOKUP(C3, A3:B5, 2, FALSE), “Price Unavailable”)) |
| 4 | 456 | $50 | |
| 5 | 789 | $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:
| A | B | C | |
|---|---|---|---|
| 1 | |||
| 2 | Employee Name | Salary | Search Name |
| 3 | John | $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”)) |
| 4 | Emma | $45,000 | |
| 5 | Sarah | $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:
| A | B | C | |
|---|---|---|---|
| 1 | |||
| 2 | Category | Amount | Search Category |
| 3 | Food | $50 | =IF(ISNA(SUMIF(A3:A6, C3, B3:B6)), “Category Not Found”, IFERROR(SUMIF(A3:A6, C3, B3:B6), “Amount Unavailable”)) |
| 4 | Travel | $100 | |
| 5 | Food | $30 | |
| 6 | Travel | $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:
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | |||||
| 2 | Product Code | Order # | Amount | Search Code | Total Orders |
| 3 | 123 | 101 | $50 | =C3 | =IF(ISNA(SUMIFS(C3:C8, A3:A8, D3)), “Product Not Found”, SUMIFS(C3:C8, A3:A8, D3)) |
| 4 | 456 | 102 | $100 | =C4 | |
| 5 | 789 | 103 | $35 | =C5 | |
| 6 | 123 | 104 | $60 | =C6 | |
| 7 | 789 | 105 | $45 | =C7 | |
| 8 | 123 | 106 | $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:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | ||||
| 2 | Category | Amount | Search | Count Result |
| 3 | Food | $50 | =B3 | =IF(ISNA(COUNTIF(B3:B6, C3)), “Not Found”, COUNTIF(B3:B6, C3)) |
| 4 | Travel | $100 | ||
| 5 | Food | $30 | ||
| 6 | Travel | $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:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | ||||
| 2 | Category | Amount | Search | Average |
| 3 | Food | $50 | =B3 | =IF(ISNA(AVERAGEIF(B3:B6, C3)), “Not Found”, AVERAGEIF(B3:B6, C3)) |
| 4 | Travel | $100 | ||
| 5 | Food | $30 | ||
| 6 | Travel | $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:
| A | B | C | |
|---|---|---|---|
| 1 | |||
| 2 | Item | Stock | Search Item |
| 3 | Apple | 50 | =IF(ISNA(INDEX(A3:A6, ROW(C3))), “Item Not Found”, INDEX(A3:A6, ROW(C3))) |
| 4 | Orange | 30 | |
| 5 | Banana | 40 | |
| 6 | Grapes | 20 |
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:
| A | B | C | |
|---|---|---|---|
| 1 | |||
| 2 | Month | Sales | Search Month |
| 3 | January | $500 | =IF(ISNA(INDIRECT(“B” & MATCH(C3, A3:A6, 0))), “Month Not Found”, INDIRECT(“B” & MATCH(C3, A3:A6, 0))) |
| 4 | February | $600 | |
| 5 | March | $450 | |
| 6 | April | $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:
| A | B | C | |
|---|---|---|---|
| 1 | |||
| 2 | Grade | Letter Grade | Search Grade |
| 3 | 85 | A | =IF(ISNA(LOOKUP(C3, A3:A5, B3:B5)), “Grade Not Found”, LOOKUP(C3, A3:A5, B3:B5)) |
| 4 | 45 | F | |
| 5 | 72 | C |
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.
- If the grade in C3 is not found in the range of test scores (A3:A5), the ISNA function returns TRUE, and the IF function displays “Grade Not Found.”
- If the grade is found, the ISNA function returns FALSE, and the LOOKUP function retrieves the corresponding letter grade.
🌟 Part 3: Tips and Tricks 🌟
- Use the ISNA function in combination with IF or other logical functions to handle errors effectively and provide meaningful feedback in your Excel worksheets.
- Consider using conditional formatting to highlight cells that contain the #N/A error for better data visualization.
- When working with large datasets, the ISNA function can be beneficial in quickly identifying missing or incorrect data.
- Combine the ISNA function with other error-handling parts like IFERROR or IFNA to handle various errors gracefully.