Here’s how you can use IFNA to make your VLOOKUPS a bit cleaner in Excel:
You can download the file here to follow along. If you get a preview, look for the download arrow in the upper right hand corner.
Most of us are familiar with VLOOKUP in Excel. For a quick refresher, you can view an earlier post here on the basics of VLOOKUP.
Sometimes when the data your are looking for is not in the list, you will receive a #N/A error, as in this example:
The issue is that “Syd” is not in the list of employees that received a bonus in the range of A1:D6. If you want to clean up the report and not show errors, you could wrap VLOOKUP in IFERROR, but that would check all errors, not just the #N/A error. Instead,wrap VLOOKUP in IFNA. Here is how that would work:
The syntax for IFNA is =IFNA(value, value_if_na). The “value” in our formula is the VLOOKUP function, while the “value_if_na” is the statement “Did not receive a Bonus”. Now this is much cleaner and more informative to the user.