How to Use Excel IFNA Function: Usabilities, Examples, and Writing Steps - Compute Expert

How to Use Excel IFNA Function: Usabilities, Examples, and Writing Steps


Home >> Excel Tutorials from Compute Expert >> Excel Formulas List >> How to Use Excel IFNA Function: Usabilities, Examples, and Writing Steps





In this tutorial, you will learn about the excel IFNA function completely. We will discuss its usabilities, examples, writing steps, combination with another formula, and also its alternative.

When processing data in excel, we sometimes need to anticipate the errors we produce during our work. There are several types of errors in excel and IFNA can anticipate for us one error type that often shows up.

Want to learn more about IFNA so you can anticipate errors better in excel? Read this tutorial until its last part!

Disclaimer: This post may contain affiliate links from which we earn commission from qualifying purchases/actions at no additional cost for you. Learn more

Want to work faster and easier in Excel? Install and use Excel add-ins! Read this article to know the best Excel add-ins to use according to us!

Learn Excel Dashboard Course






What is the IFNA Function in Excel?

IFNA is a function that can catch the #N/A error in excel and substitute the error value with another value.

#N/A is the error code for the “Not Available” error. This means excel cannot find the data we want in a specified cell range. Excel lookup formulas are usually the ones that produce this error as their main function is to find data for us.



IFNA Usability

IFNA can help us to replace a #N/A error with another value if the error happens.



IFNA Result

IFNA will produce our original value if it doesn’t find the #N/A error value in it. If it finds the error, however, then it will produce the substitute value that we input into it.



Excel Version from Which We Can Use IFNA

We can use IFNA since excel 2013.



The Way to Write It and Its Input

Here is the general writing form of IFNA in excel.

= IFNA ( something_to_anticipate_the_na_error_from , substitute_value )


We usually input a lookup formula into IFNA as we usually anticipate the #N/A error from it. However, you can input any kind of data or formula you want as your first IFNA input.

You can also input anything you want as the IFNA substitute value if the #N/A error happens. You should input something that can help to make your data processing still go smoothly despite the error occurrence.



Example of Its Usage and Result: If NA Then 0

Here is an implementation and result example of IFNA in excel. Here, we try to substitute the #N/A error we find with 0.

How to Use Excel IFNA Function: Usabilities, Examples, and Writing Steps - Screenshot of the IFNA Implementation Example

In the example, we use HLOOKUP to find the banana’s stock quantity from the table on the left. We get a #N/A error because there is no banana in the table products.

Using IFNA, we can change the #N/A error to 0. Just input the HLOOKUP result and the 0 in the IFNA and we will immediately get the #N/A substitution!

We can also do it in one writing if we want by placing the HLOOKUP directly as the IFNA input.



Writing Steps

After discussing the IFNA writing form, inputs, and example, now let’s discuss its writing steps in excel. You can see the writing step implementation example in a screenshot so you can understand it easier.



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

    How to Use Excel IFNA Function: Usabilities, Examples, and Writing Steps - Screenshot of Step 1

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

    How to Use Excel IFNA Function: Usabilities, Examples, and Writing Steps - Screenshot of Step 2

  3. Input the data or formula from which you anticipate the #N/A error. Then, type a comma sign ( , )

    How to Use Excel IFNA Function: Usabilities, Examples, and Writing Steps - Screenshot of Step 3

  4. Input the #N/A error substitution value. IFNA will produce this value if the data or formula you inputted previously has a #N/A error value

    How to Use Excel IFNA Function: Usabilities, Examples, and Writing Steps - Screenshot of Step 4

  5. Type a close bracket sign

    How to Use Excel IFNA Function: Usabilities, Examples, and Writing Steps - Screenshot of Step 5

  6. Press Enter
  7. Done!

    How to Use Excel IFNA Function: Usabilities, Examples, and Writing Steps - Screenshot of Step 7




Using IFNA with Another Formula: IFNA VLOOKUP

As we mentioned in the IFNA example discussion, we can write IFNA and another formula in one formula writing. In fact, it is probably the most common practice if we use IFNA.

Regarding the formula we combine with IFNA, the most often one might be VLOOKUP. It is a popular lookup formula in excel and we sometimes need to anticipate the #N/A error it produces.

We usually write IFNA and VLOOKUP simultaneously by using this writing form.

= IFNA ( VLOOKUP ( lookup_reference , reference_cell_range , result_column_index , lookup_mode ) , na_substitute_value )


We write our VLOOKUP inside IFNA to anticipate the #N/A error it might produce. If it does produce the error, then IFNA will substitute it with the value we input.

Here is the IFNA and VLOOKUP combination implementation example in excel.

How to Use Excel IFNA Function: Usabilities, Examples, and Writing Steps - Screenshot of the IFNA and VLOOKUP Combination Implementation Example

We combine IFNA and VLOOKUP in the example to anticipate if VLOOKUP doesn’t find the stock quantity we want. Here, we try to find the banana stock quantity (again). But since it isn’t there on the left table, our VLOOKUP produces a #N/A error.

However, since we have already combined the VLOOKUP with IFNA, we produce another value besides #N/A instead. The IFNA substitute value here is a “Data Not Available” text and thus, this is what our IFNA VLOOKUP combination produces.

If the VLOOKUP does find the data, then we will get a normal VLOOKUP result instead of the substitute value.



How to Catch All Types of Error, Not Just #N/A Error: IFERROR

IFNA can only anticipate the #N/A error for us. What if we want to anticipate not just #N/A, but all other types of error as well? For this, we can use the IFERROR formula.



The general writing form of IFERROR in excel is similar to IFNA.

= IFERROR ( something_to_anticipate_the_error_from , substitute_value )


Here is the implementation example of IFERROR in excel. We compare it with the IFNA implementation here.

How to Use Excel IFNA Function: Usabilities, Examples, and Writing Steps - Screenshot of the IFERROR and IFNA Implementation Example

In the example, you can see how IFERROR can anticipate more error types than IFNA. Using IFNA for an error other than #N/A will still produce the error instead of the IFNA alternative value.

Thus, when you need to anticipate all types of errors, you should use IFERROR. However, if you need to know whether the error is #N/A or not, use IFNA to help you.



Exercise

After you have learned how to use IFNA in excel, let’s 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.

Link to the exercise file:
Download here

Instructions

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

Link to the answer key file:
Download here



Additional Note

Besides HLOOKUP and VLOOKUP, the lookup formulas we usually use in excel are LOOKUP, INDEX MATCH, and XLOOKUP. You might also need IFNA when using one of these formulas to anticipate their #N/A error.



Related tutorials you should learn:



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!




Learn Excel Dashboard Course

Follow our tutorial content also on


Excel Calculation

Sum in Excel

Subtraction in Excel

Multiplication in Excel

Division in Excel

Average in Excel



Excel Formula

VLOOKUP Function in Excel

IF Function in Excel

SUM Function in Excel

COUNTIFS Function in Excel

SUMIFS Function in Excel



Excel Tips and Trick

How to Print in Excel

Convert Number to Text in Excel

Excel Worksheet Definition

Excel Range Definition

Excel Shortcuts



Excel Products & Services Recommendation

Best Laptops for Excel

Best Tablets for Excel

Best Keyboards for Excel

Best Mouse for Excel

Best Monitors for Excel



Excel Consultation

Recommended Things

About Us

Contact Us

Privacy Policy

Affiliate Disclosure

Terms & Condition



© 2024 Compute Expert