How To Use The VLOOKUP Function Wrapped In The IFNA Function In Excel

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:

ifna1

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:

ifna2

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.

Happy Excellling!

Related Post

Free Download!

Subscribe to Download Your FREE Copy of
"My 70+ Favorite Excel Keyboard Shortcuts" Today!

, , ,

No comments yet.

Leave a Reply