1. Home
  2. /
  3. Excel Functions
  4. /
  5. IFNA Function
Excel_IFNA_function_featured_image

The IFNA function is a nice way to capture and manage #N/A errors without capturing other errors. The IFNA function can be used to capture errors resulting from performing lookups with MATCH, VLOOKUP, HLOOKUP, XLOOKUP, etc functions. The IFNA function lets users return a custom result or perform a different task the formula generates a #N/A error and a regular result when no error is generated. IFNA function will handle the #N/A error only and other errors, if any, will be shown as usual.

Example:

As shown above, the IFNA function captures the #N/A error that occurs with the VLOOKUP function. For now, just pay attention to the IFNA function and ignore the VLOOKUP function. We will discuss the VLOOKUP function in a separate tutorial.

=IFNA(VLOOKUP(F15,$B$5:$C$17,2,0),"Not Found")

When the value from column F is found in the range $B$5:$C$17, specifically in column B, it gives the rate from column C corresponding to the matched value in column B. If the lookup value, in this case the value from column F, is not found then VLOOKUP returns an #N/A error and the IFNA function captures this error and returns a more user-friendly result. In this case, #N/A is replaced with “Not Found”.

IFERROR or IFNA?

The IFERROR function is definitely a useful function and it catches all kinds of errors. While this is useful, it may sometimes lead to inaccurate/unexpected results. If, for example, there is a typo in a formula, Excel will return #NAME? error, but IFERROR will suppress the error and return the alternative result. This can hide an important problem. In many cases, it is more meaningful to use the IFNA function, which only captures the #N/A error.

Excel provides a number of error-related functions, each with a different purpose:

  • The ISERR Function will return True for all types of errors except #N/A error.
  • The ISERROR Function will return True for all types of errors.
  • The ISNA Function will return True only for #N/A error.
  • The ERROR.TYPE Function will return the numeric code for an error.
  • The IFERROR Function captures an error and gives the option to place an alternative result.
  • The IFNA Function captures #N/A errors and gives the option to place an alternative result.

Help others find us!

Picture of Md. Saiful Islam

Md. Saiful Islam

Mr. Saiful Islam is a professional trainer of Excel and other office applications. In addition to conducting training, he has over 15 years of experience in Project Finance, Investment Management, Treasury Management, Budgetary Control, and other key areas of Finance & Accounts. He often writes about Careers, Productivity Tips, Businesses, Technologies, Banking, Investments, and more. He is available to conduct in-house training for any organization and can be reached at +8801673844325 or via his LinkedIn Profile.

Leave a Reply

Subscribe to Receive Free Tutorials