Look Who it Was Random video

Excel - Prevent #N/A Errors in VLOOKUP: Excel 2003 vs Excel | MrExcel Netcast - Episode 853

2,951 views · Published 8 January 2009 · 3:11 · Indexed 1 November 2026

Channel: MrExcel.com · 2009 · Education

Watch on YouTube

Microsoft Excel Tutorial - Prevent #N/A Errors in VLOOKUP.

Welcome back to the MrExcel netcast! In this video, we'll be discussing a question that was asked at the Springfield, Missouri conference by Chris. She wanted to know if there was a way to prevent the #N/A error in VLOOKUP. And while this may be a painful process in Excel 2003, Excel 2007 has a much easier solution.

In Excel 2003, we would have to insert a new bit of formula using the ISNA function to check if the VLOOKUP is equal to #N/A. This involves holding down the Shift key and selecting the formula, then using the Ctrl+C and Ctrl+V shortcuts to copy and paste the formula. But in Excel 2007, there's a much simpler solution.

Excel 2007 introduced a new function called IFERROR, which allows us to specify what to do if there is an error in the formula. So instead of having to do the VLOOKUP twice, we can simply use the IFERROR function with the VLOOKUP as the first argument and a blank or 0 as the second argument. This makes it much faster and easier to get rid of those pesky #N/A errors.

However, it's important to note that this function is only available in Excel 2007 and newer versions. If you send a workbook with this function to someone using Excel 2003, they will receive a name error. So if you need to share your workbook with others, you may need to use the ISNA function instead. But for those using Excel 2007, IFERROR is a great solution to preventing #N/A errors in VLOOKUP.

Thank you for watching this netcast from MrExcel. Don't forget to subscribe to our channel for more helpful Excel tips and tricks. And if you have any questions or suggestions for future videos, leave them in the comments below. See you next time!

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/


You love VLOOKUPs, but you hate the #N/A error that results. In our Where is it Wednesday edition of the MrExcel podcast, I will show you two ways to prevent #N/A as the result of your VLOOKUP formulas. One harder method in Excel 2003 and an easier method in Excel 2007. Episode 853 shows you how.

Table of Contents:
(00:00) Introduction and thank you to George Wood
(00:26) Excel 2007's improvement on VLOOKUP
(00:41) Painful process in Excel 2003
(01:05) Using IF statement in Excel 2003
(01:36) Excel 2007's improvement with IFERROR function
(02:20) Limitations of using IFERROR function
(02:30) Solutions for both Excel 2003 and 2007
(02:40) Clicking Like really helps the algorithm

#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial

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

More from this channel