Excel IFNA Function


Home >> How to Use Excel Tutorial >> Excel Formulas List >> Excel IFNA Function



In this tutorial, you will learn the IFNA excel formula. Excel IFNA function is useful to give an alternative value to the #N/A or Not Available error which can be produced by other functions in this spreadsheet software.


Why do We Need to Learn IFNA Excel Formula?


In doing data processing on a spreadsheet, sometimes we find errors when there are mistakes in formulas usages. One of the errors that often comes out is #N/A or Not Available. It shows up if the spreadsheet does not find the data that we want to find. This often happens when we use lookup formulas like MATCH or VLOOKUP to search a data but they cannot find it. This error, of course, can make the interface of the data processing result to look bad because there are many error marks in our spreadsheet. Besides that, the further analysis process that we want to do becomes not smooth and wrong because there are these errors in the previous process.

As the illustration of this problem in our job, let’s say we are analyzing the best promotion al activities for few company products using spreadsheet and the process needs to be helped with lookup formulas to get the details of all of the products for the analysis process. If there are product details that the formulas do not find, then the formulas will return #N/A errors. When we want to use the lookup results on other formulas with the purpose of supporting the analysis to find the best promotional activities for all of the products, then #N/A can make other formulas that are used to produce errors too. This will create the wrong final results of the promotion activities analysis in our job.

If we want to anticipate this and give alternative results so no #N/A will show up in our processing, then we can use the solution from excel IFNA function. IFNA excel formula can help you to give an alternative value if there is an #N/A in our spreadsheet. IFNA excel formula is very useful to be understood and used especially if you often use lookup formulas to help your processing in the spreadsheet.


What is the Excel IFNA Function?


Excel IFNA function is a function with the usage objective to give an alternative value if there is a Not Available error. Excel IFNA function can only be used to anticipate this kind of error and it will not give an alternative value for other kinds of errors.

Generally, the inputs of IFNA excel formula can be explained briefly as follows:


=IFNA(value, value_if_na)


Notes:
  • value = the value to be anticipated for the #N/A
  • value_if_na = alternative that will be given if the original value truly has an #N/A value



How to Use the IFNA Excel Formula?


The following will explain the step-by-step of the writing of excel IFNA function. IFNA excel formula needs two required inputs which are the value or formula that will probably produce an #N/A error and the alternative value that will be given if it produces an #N/A.


Using Excel IFNA Function

  1. Type equal sign ( = ) in the cell where you want to put the result in

    Excel IFNA Function - Screenshot of Step 1

  2. Type IFNA (can be with large and small letters) and open bracket sign after =

    Excel IFNA Function - Screenshot of Step 2

  3. Type data or formula or cell coordinate where the data or formula that you want to anticipate an #N/A error value from is, then type a comma sign ( , )

    Excel IFNA Function - Screenshot of Step 3

  4. Type the alternative value or cell coordinate where the value that will be produced if your previously inputted data input or formula has an #N/A error value is

    Excel IFNA Function - Screenshot of Step 4

  5. Type close bracket sign

    Excel IFNA Function - Screenshot of Step 5

  6. Press Enter
  7. The process is done!

    Excel IFNA Function - Screenshot of Step 7



Exercise


After you have learned how to write the IFNA excel formula, now is the time you deepen your understanding by doing the following exercise.

Download the exercise file and do all of the instructions. Download the answer key file to check your answers if you have done the exercise or if you are confused about how to do it.

Link for the exercise file:
Download here

Instructions

  1. Copy all the data in the NA 1 column on column No. 1 and replace all the Not Available data with “Not Found”!
  2. Add all the data in the NA 2 column with 1000 on column No. 2. Assume all the #N/A data is 0!
  3. Add all the parallel data in the NA 3 and NA 2 columns with the results written in column No. 3. Assume all the #N/A data in NA 2 is 0 and in NA3 is 1000!

Link for the answer key file:
Download here


Additional Note


If you want to anticipate other kinds of errors too, not only #N/A, then you can use the IFERROR formula for your need.



Want to Learn More About Excel?


Get updated excel info from Compute Expert by registering your email. It's free!


Want to Learn More About Excel?


Get updated excel info from Compute Expert by registering your email. It's free!



Follow our tutorial content also on


CONTACT US PRIVACY POLICY TERMS AND CONDITION AFFILIATE DISCLOSURE @Compute Expert