Excel - Prevent #N/A! Errors in Excel - Episode 2042

1,510 views · Published 7 October 2016 · 4:38 · Indexed 8 November 2026

Channel: MrExcel.com · 2016 · Education

Watch on YouTube

Microsoft Excel Tutorial: Microsoft Excel - Fix #N/A! Formula Errors with IFERROR and IFNA Functions.

Microsoft Excel - Fix #N/A! formula errors in Excel.

Welcome to another episode of the MrExcel podcast, where we dive into all things Excel. In this episode, we'll be discussing the IFERROR and IFNA functions and how they can help fix or prevent formula errors in your spreadsheets. Don't forget to check out the entire playlist for more helpful tips and tricks from my book.

In the past, if you wanted to prevent the #N/A! error from a VLOOKUP formula, you had to use a lengthy and complicated method. This involved copying and pasting the formula, adding an IF(ISNA()) function, and then repeating the formula. Not only was this time-consuming, but it also meant that the VLOOKUP was being calculated twice for every formula. But fear not, Excel 2010 introduced the game-changing IFERROR function.

The IFERROR function allows you to specify what to do if a formula returns an error, making it much simpler and more efficient than the previous method. It also means that the formula is only calculated once, saving you time and improving the performance of your spreadsheet. However, there is one catch - IFERROR treats all errors, including #DIV/0!, as the same. But don't worry, Excel 2013 introduced the IFNA function to solve this issue.

IFNA works just like IFERROR, but it only detects #N/A! errors. This means that if you have Excel 2013 or newer, you can use IFNA to prevent #N/A! errors without affecting other types of errors. However, if you're sharing your workbook with others who may not have Excel 2013, it's best to stick with IFERROR. This tip was suggested by readers Justin Fishman, Stephen Gilmer, and Excel by Joe, so a big thank you to them for their contribution.

If you want to learn more about Excel and discover even more helpful tips and tricks, be sure to check out my book, which is filled with 200 pages of easy-to-read, full-color content. And don't forget to click the "i" in the top-right corner to purchase the book. Thank you for tuning in to this episode of the MrExcel podcast, and we'll see you next time for another netcast filled with spicy tips and tricks.

Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/

You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-on-youtube/

#excel
#microsoft
#microsoftexcel
#exceltutorial
#exceltips
#exceltricks
#excelformula
#excelformulasandfunctions
#evergreen

This video answers these common search terms:
how to fix #n/a in index match excel
excel how to remove n/a
formula to remove #n/a in excel
how do i clear the n/a on a vlookup excel
how to change a n/a to a 0 in excel
how to convert n/a in excel to 0
how to correct a #n/a error in excel
how to fix #n/a in excel
how to handle excel #n/a error
how to hide the n/a in excel
how to remove #n/a error in excel vlookup
how to replace n/a with blank in excel
removing #n/a in excel
what is a #n/a error in excel

Table of Contents:
(00:00) Preventing #N/A! errors in Excel
(00:10) Old method for preventing #N/A! errors in Excel 2007 or earlier
(00:20) Using IFERROR in Excel 2010 or newer to prevent errors
(01:05) Limitations of IFERROR and introduction of IFNA in Excel 2013
(02:18) Handling errors in formulas to prevent #DIV/0! errors
(03:18) Buy the book!
(04:01) Clicking Like really helps the algorithm

How do you prevent #N/A! error from your VLOOKUP? Three ways are discussed in this episode. 
In the old days, you would use =IF(ISNA(Formula),0,Formula)
Starting in Excel 2010, =IFERROR(Formula,0)
But IFERROR treats DIV/0 errors the same was a #N/A! errors
Starting in Excel 2013, use the =IFNA(Formula,0) to detect only #N/A errors
Thanks to Justin Fishman, Stephen Gilmer, and Excel by Joe.

Join the MrExcel Message Board discussion about this video at  https://www.mrexcel.com/board/threads/1153869/

More from this channel