How To Use IFNA function in excel
Table of Contents
The IFNA function in Excel is a logical function that allows you to handle the #N/A error that occurs when a value is not found in a lookup formula. The IFNA function returns a specified value if the formula results in the #N/A error, otherwise, it returns the result of the formula. Here’s how to use the IFNA function in Excel:
-
- Select the cell where you want to display the result of the function.
- Type the following formula into the cell: =IFNA(formula,value)
- In place of “formula,” enter the formula that you want to check for the #N/A error.
- In place of “value,” enter the value that you want to display if the formula results in the #N/A error.
- Press enter.
=IFNA(value, value_if_na)
Here’s an example of how to use the IFNA function:
Suppose you have a list of names and corresponding scores, and you want to find the score of a particular student. However, the name of the student is not in the list, which causes a #N/A error to appear in the formula. You can use the IFNA function to handle this error by displaying a message instead of the error.
Select a cell where you want to display the result of the function.
Type the following formula into the cell: =IFNA(VLOOKUP(“John”,A2:B10,2,FALSE),”Not found”)
This formula uses the VLOOKUP function to search for the name “John” in the range A2:B10 and return the corresponding score. If the name is not found, the IFNA function will return the message “Not found” instead of the #N/A error.
Copy the formula to other cells in the column to apply the same logic to other students’ scores.
Note that in the above example, we assumed that the names are in column A and the scores are in column B. You will need to modify the formula to suit your specific situation.