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
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
-
2:13
Excel Calculate Decimal Hours from two Time Cells - Episode 902
-
3:57
Excel 2007 - Color of Selected Sheets is too much like unselected sheets - Episode 907
-
2:48
Excel - Adding Equals Icon to Excel QAT - Build Formula with Mouse - Episode 911
-
3:30
Excel Grouping Percentages into Buckets for a Pivot Table - Method 2 - Episode 913
-
2:04
Excel Change Color of Selected Cells - Episode 914
-
4:38
Excel - Auto Numbering a List with Frequent Inserts & Deletes - Episode 916
-
2:00
Excel Hiding Data in Plain Site - Episode 919
-
2:20
Excel - Bring Back The Full Excel 2003 Dialogs In Excel! - Episode 920