The IFNA function
The =IFNA(VLOOKUP(G13,B3:E12,3,FALSE),"¯\_(ツ)_/¯") formula in cell H13 returns a whimsical emoji shrug, ¯\_(ツ)_/¯, in place of the #N/A error. In comparison, the =IFNA(VLOOKUP(G13,B3:E12,4,FALSE),"Not Found") formula in cell I14 returns a more practical message of Not Found when VLOOKUP returns #N/A.
Nuance
Sometimes, users inadvertently bump the spacebar when entering data in Excel, which can result in trailing spaces that are tricky to track down. Inconsistencies such as this cause users to assume Excel is broken or that they simply don’t understand lookup functions. When you double-click on a cell, check to see whether the cursor is positioned immediately adjacent to the last character in the cell. If not, press backspace as needed to eliminate the extra spaces, or use the TRIM function.
IFNA has two arguments:
- Value – This is a calculation that could return
#N/A, such...
