Sanjida Mehrun Guria
While working with Excel, we often face #N/A error when the required value is missing from the dataset. But if you want to get rid of this visible error and replace it with any definite text, then the ISNA function is a good option. It gets more logical and dynamic when combined with the IF function. In this article, we will learn how to use IF with ISNA function in Excel through 3 ideal examples.
Introduction to IF Function in Excel
In this section, you will get a short overview of IF function.
- Function Objective:
Checks whether a condition is met, and returns one value if TRUE, and another one if FALSE.
- Syntax:
=IF(logical_test, [value_if_true], [value_if_false])
- Arguments Explanation:
| ARGUMENT | COMPULSORY/OPTIONAL | EXPLANATION |
| logical_test | Compulsory | Given condition for a cell or a range of cells |
| [value_if_true] | Optional | Defined statement if the condition is met |
| [value_if_false] | Optional | Defined statement if the condition is not met |
- Logical Operators:
| OPERATOR | DESCRIPTION |
| = | Equal to |
| <> | Not Equal to |
| > | Greater Than |
| >= | Greater Than or Equal to |
| < | Less Than |
| <= | Less Than or Equal to |
- Return Parameter:
Logical values: TRUE or FALSE if statements are not defined. If statements are defined, they’ll be shown as return values based on the conditions met or not.
- Versions:
This version is available from Excel 2000.
Introduction to ISNA Function in Excel
The ISNA function is categorized under the INFORMATION functions in Excel. It checks whether a cell contains the #N/A error or not and returns TRUE or FALSE depending on the presence of #N/A.
- Function Objective:
Checks whether a value is #N/A, and returns TRUE or FALSE.
- Syntax:
=ISNA (value)
- Arguments:
| ARGUMENT | REQUIRED/OPTIONAL | EXPLANATION |
| value | Required | A value or expression to be checked for #N/A error |
- Versions:
This version is available from Excel 2003.
How to Use IF with ISNA Function in Excel: 3 Ideal Examples
So far, we got a brief idea about the IF and ISNA functions. Now let us combine them for this sample dataset. Here, the dataset shows 10 Student Names with their Obtained Marks in an exam.
Considering it as a long dataset, let us find the marks that the student Albert obtained in the exam. For this, we can use any type of function that works with lookup value. For example, here we will use the VLOOKUP function and therefore get the output with this formula.
=VLOOKUP(F4,B5:C14,2,0)
Now, if you insert the name Pamela, the output returns a #N/A error as the name is not available in the dataset.
This is where we need to use the IF and ISNA functions to replace this error. Let’s see the following examples to do the task.
1. Use IF with ISNA Function to Replace Error in Excel
In this example, we will apply the IF with the ISNA function to replace the error that occurred. For this, do the following task.
- First, select Cell F6.
- Then, put this formula inside the cell.
=IF(ISNA(F5),"Not Found", "Found")
- Lastly, press Enter.
- That’s it, you will get the result as Not Found instead of a #N/A error.
Read More: How to Use NOT and ISNA Functions in Excel
2. Combine IF and ISNA with VLOOKUP Function in Same Table
We can also use the VLOOKUP function along with the IF and ISNA functions for errors. Let’s see how it works.
- In the beginning, apply this formula in Cell F6.
=IF(ISNA(VLOOKUP(F4, $B$5:$C$14, 2, FALSE)), "Not Found", VLOOKUP(F4, $B$5:$C$14, 2, FALSE))
- Then, hit on Enter.
- As a result, you will get the output as text rather than as a #N/A error.
In this formula, the VLOOKUP function looks for the lookup value of Cell F4 from the range B5:C14. Along with it, the function returns a value from column number 2, otherwise FALSE when not found. Finally, the IF function runs a logical test based on the ISNA function.
3. Apply IF and ISNA with MATCH Function for Logical Test in Excel
Here is another example where we will use the MATCH function along with the IF and ISNA functions in Excel. Let’s see the process below.
- Initially, select Cell F6 and insert this formula.
=IF(ISNA(MATCH(F4,$B$5:$C$14,0)), "Not Found", "Found")
- Lastly, press Enter and you will get the following output.
In this formula, the MATCH function finds and matches the lookup value of Cell F4 from the range B5:C14. Then, we typed 0 for an Exact Match. Finally, the IF function runs a logical test based on the ISNA function.
Read More: Use ISNA and MATCH Functions in Excel
Excel IFNA Function: An Alternative to ISNA-IF Combination
People often mix up the ISNA function with the IFNA function. Because both of them are applied to check for #N/A error and return TRUE or FALSE. But the major difference is, we need to nest the ISNA and VLOOKUP (or MATCH/any LOOKUP function) functions with the IF function to get the output. On the other hand, the IFNA function directly works without any nested function. The following image shows the output with the IFNA function.
Download Practice Workbook
Get this sample file practice by yourself.
Conclusion
Finally, we are at the conclusion of our article. Here, we tried to explain how to use IF with the ISNA function in Excel with 3 ideal examples. Let us know your feedback in the comment box.
