Skip to content

Working With the NA Error

Have you ever come across this error before? #N/A

If so, I will show you how you can deal with this error using the IFNA function in Microsoft Excel.

The #N/A Error

The #N/A error shows up whenever there is no value available, and it commonly appears whenever you are working with lookup functions such as VLOOKUP, HLOOKUP, etc.

Sometimes, this error can be helpful. For example, if you want a graph to display nothing rather than a zero, then you can capitalize on this error. You can even use the NA function to generate the #N/A error.

However, most of the time, you don’t want the #N/A error lurking around in your spreadsheet. Here is how you can deal with it!

The IFNA Function

The IFNA function accepts two arguments.

  • Value – This can be another function or formula that evaluates to a value.
  • Value_if_na – This is what you want returned if the first argument is #N/A.

You can use the IFNA function by wrapping it around the outside of your existing formula, as you see below.

=IFNA( Your_Formula, Value_if_na )

If your function or formula results in the #N/A value, the IFNA function will return whatever you set as the second argument. If there is no #N/A error, then the IFNA function will simply return whatever the first argument originally evaluated to.

Here is a great example.

This can be helpful to anyone who uses the spreadsheet.

You can have the IFNA function return something a lot more helpful than #N/A. The prompt “No Match Found” is a lot more helpful than #N/A, especially for someone who may not be familiar with this error code.

So the next time you find yourself trying to deal with the #N/A error, use the IFNA function to help smooth things out.

This Post Has One Comment

  1. I didn’t know that this was a thing! I usually use “IFERROR”, but sometimes it would be good to know when there’s an error other than “N/A”. I’m going to start using this for sure!

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Back To Top