The IFNA function
Around a lookup: "not found" becomes whatever you want, and a real error (broken range, typo) stays visible.
Syntax
=IFNA(value, value_if_na)
In a French Excel: =SI.NON.DISP(valeur; valeur_si_na).
Arguments
- value: the formula, often a VLOOKUP.
- value_if_na: what to return instead of #N/A.
An example
=IFNA(VLOOKUP(A2,Customers!$A:$C,3,FALSE),"Unknown")
"Unknown" for a missing customer; a #REF! would stay visible.
Pitfalls
- Missing from Excel 2010 and earlier.
- Only replaces #N/A: that is the point.
Going further
Frequently asked questions
- When should I use IFERROR instead?
- For a calculation whose only possible error is known, such as a division by zero.