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.