IFNA Function


VLOOKUP returns the #N/A error if it can’t find a value in a lookup table. The IFNA  function allows you to catch errors and return your own custom value when there is an error. If VLOOKUP returns a value normally, there is no error and the looked up value is returned. If VLOOKUP returns the #N/A error, IFNA takes over and returns the value you supply.

Purpose: To hide the #N/A error that VLOOKUP results when it can’t find a value, you can use the IFNA function to catch the error and return any value you like.

IFNA Function Syntax


Tutorial of IFNA Function in Excel :

In the first screen, you can see we used VLOOKUP Function in column C.

In Cell C6 we get value  #N/A because we did not define CNY conversion rate in column G.

In column D we used IFNA with VLOOKUP function.

In Cell D6 we set predefined value “0” with use of IFNA along with VLOOKUP to replace #N/A to “0”.

It can be also called nested IFNA with VLOOKUP.

Recommended For You

About the Author: Mehul Vaghela

Howdy Math fan. I'm Mehul Vaghela - B.E.Mechanical. Enthusiastic and holds the passion of reading, writing, and learning. In 10 years of experience on the field, I've faced so much problem with managing data and learned to make that happen. ExcelElixir is one of the parts of passing it on (knowledge).

Leave a Reply

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