If you are a regular Excel user and want to return a value as the replacement for the #N/A error in Excel, then there should be a way for it. In this post, we will let you know a simple solution to your problem which is nothing but a built-in function of Excel. Yes, you can use the IFNA function that helps to replace the N/A error with the specific value in the spreadsheet as per your preference.
Today, we come up with the syntax, explanation, and example scenarios of the IFNA function in Excel. Let’s catch them below. Get an official version of MS Excel from the following link:
https://www.microsoft.com/en-in/microsoft-365/excel
Jump To
What is IFNA Function?
IFNA is one of the built-in functions in Microsoft Excel. If you want to return a specific value in the spreadsheet if the N/A error occurs in the formula. The term “N/A” represents that there is no value available in the range. Whenever Excel didn’t find the exact match, it returns a #N/A error. In order to replace this error with the specified value, we can use this IFNA function.
Syntax:
To apply this function, select a cell, type the formula, and hit the Enter button.
=IFNA(value, value_if_na)
Arguments:
- value – It represents the input value or a formula to check for an error.
- value_if_na – The value to return if #N/A error occurs.
Motive:
- It returns the specified value if the given formula gives a #N/A error.
Return Value:
- We will get the specified value in the formula as output.
What Will IFNA Function DO?
As we said earlier, the IFNA function returns an alternate value if the entered formula gives a #N/A error in the spreadsheet. This function is categorized as “Logical” in Excel. In order to use this function, we can give a value or a formula to check for an error.
For instance, we have come with the employee IDs with their names and salary details. From that list, we want to get a particular employee’s salary based on the given ID. As per the analysis, we need to use a formula or value inside the IFNA function. In order to search for a specific value in the range, we suggest you use the VLOOKUP formula. To be more precise, the VLOOKUP function will look for the specific value in the range based on given conditions and returns the result. You can find the syntax and explanation of the VLOOKUP function at the end of this section
Now, we will get the salary of the person from the range based on ID 11. If the IFNA function doesn’t find the match, then we will need to print the value “not found.” For that, we need to apply the following formula.
=IFNA(VLOOKUP(G2,B3:D10,3,FALSE),”not found”)
Now, click on the Enter button to execute it and get the result. In the below image, we will get the output value as “Not found“, because there is no ID called 11 in the specified range.
We hope that the given scenario helped you to understand the IFNA function. If still you have any confusion, then move to the below section, where we will show you a simple example.
VLOOKUP Syntax: VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])
Generally, the VLOOKUP function is used to find the exact match in the range in Excel. Following are the parameters and their usage in the VLOOKUP function.
1) lookup_value – The value that you want to find.
2) table_array – It is the input range.
3) col_index_num – The column number that has the return value.
4) range_lookup – It is an optional one. Here you can give “TRUE or FALSE“. TRUE returns the approximate match whereas FALSE returns the exact match.
Alternate Example
Let’s see a simple example to know how to use the IFNA function in the spreadsheet.
- The following screenshot has shown the list of Products that are offered by a cosmetic company. You can see the product names with their ID and price.
- From this data, we will need to extract a specific product’s price based on its name. If there is no product available with the specified name, then we need to return the value “No match found.”
- We will use the VLOOKUP formula as an input value for the IFNA function to find the match in the range.
- In order to get the result, we need to apply the following formula. Here we want to check whether there is any product available in the list with the name “mascara.”
=IFNA(VLOOKUP(G2,B3:D10,3,FALSE),”No match found”)
- If there is no product available with the name “mascara”, then the VLOOKUP function leads to a #N/A error, but the IFNA function will not provide the error value, instead, it returns the “No match found” value in the cell.
- Once you have entered the formula, hit the Enter button to get the result, as shown in the below screenshot.
By doing so, you can easily get the specific value for the replacement of the N/A error in Excel. Hope it is easy for you.
Citations
We have referred to the below-mentioned articles to fully understand the purpose of the Excel IFNA function. They helped us a lot. You can also check out them using the given links: IFNA function[1], NA Error[2].
Wind-Up
With the help of the given scenarios and examples, you can easily understand the purpose of the IFNA function in Excel and how to apply it in the spreadsheet. The screenshots help to learn it even easier. Share your suggestions in the below comment section. Thanks for visiting AawExcel. Keep Learning!!
If you want to know what people think about this IFNA Function of Excel, then we suggest you look for it on the Quora interaction webpage. From where you can get to know more regarding the Excel IFNA function. Here, we have provided the link to Quora’s official website.
Video Tutorial
The following video will show you how to use the Excel IFNA function in the spreadsheet with proper examples.
A few more Logical Functions for you:
Hi there, I’m Sridhar – an Excel enthusiast with over 10 years of experience working with software. I’m passionate about using Excel to solve complex problems and streamline business processes. Over the years, I have helped businesses of all sizes to improve their operations and save time and money.
Aside from working with Excel, I also enjoy writing and sharing my knowledge with others. You’ll often find me contributing to the AAW Excel blog, where I provide tips, tricks, and tutorials that are easy to understand for readers of all skill levels.
