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.
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
Transcript of the video:
Hey, all right, well, welcome back to the MrExcel netcast.
I want to thank George Wood for the "Where is it Wednesday" theme.
This isn't necessarily "Where is it Wednesday", it's actually "I Admit Excel 2007 Does This One Better".
This is a question that Chris asked at the Springfield, Missouri, conference.
She said, "You know, VLOOKUP, there's a way to make sure that VLOOKUP never gives us an NA." And if you're in Excel 2003, this is relatively a painful process.
You have your VLOOKUP and what we have to do is insert a new bit of formula: =If (ISNA{ -- and we want to check and see if that VLOOKUP is equal to NA.
So, here I'm going to hold down the Shift key, hold down the N key, and select all of that formula right here, while editing the formula.
Do Ctrl+C, now I press End, and put a closing parenthesis to finish the ISNA.
Now, I'm working in the If statement.
If it's NA, then we want to put something else-- maybe a 0 or a quote quote.
I'll put a quote quote here, and then another comma.
If it's not an NA, well, then we want Excel to do the VLOOKUP.
So I do Ctrl+V, and basically what's going to happen is it's going to do the VLOOKUP twice; it's going to do the VLOOKUP first and if it's an NA, it puts nothing like we have right here; if it's not an NA then it goes ahead and does the VLOOKUP.
Well, they improve this dramatically in Excel 2007.
Here's what we can do: We have the same formula in Excel 2007, we don't have to have Excel do the VLOOKUP twice; we use a brand new function that they added to Excel 2007 called IFERROR.
Okay, now I know we've had ISERROR for a long time, but this is brand-new-- called IFERROR.
Basically, we have our VLOOKUP as the first argument and then a comma, and here, what to do if we get any kind of an error from that first formula.
So quote quote, and what we'll see is this NA down here in row 19.
When I copy this formula down it will change to a blank.
IFERROR, much faster and much easier way to get rid of those NAs in Excel 2007.
Now, of course, the obvious problem is, if you use this function you can only send this workbook to other people that have Excel 2007; otherwise, the old Excel will return a name error-- doesn't know anything about IFERROR.
So, great question from Chris-- a couple of ways to solve, whether you're in Excel 2003 or Excel 2007.
I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel.
[ music ]
I want to thank George Wood for the "Where is it Wednesday" theme.
This isn't necessarily "Where is it Wednesday", it's actually "I Admit Excel 2007 Does This One Better".
This is a question that Chris asked at the Springfield, Missouri, conference.
She said, "You know, VLOOKUP, there's a way to make sure that VLOOKUP never gives us an NA." And if you're in Excel 2003, this is relatively a painful process.
You have your VLOOKUP and what we have to do is insert a new bit of formula: =If (ISNA{ -- and we want to check and see if that VLOOKUP is equal to NA.
So, here I'm going to hold down the Shift key, hold down the N key, and select all of that formula right here, while editing the formula.
Do Ctrl+C, now I press End, and put a closing parenthesis to finish the ISNA.
Now, I'm working in the If statement.
If it's NA, then we want to put something else-- maybe a 0 or a quote quote.
I'll put a quote quote here, and then another comma.
If it's not an NA, well, then we want Excel to do the VLOOKUP.
So I do Ctrl+V, and basically what's going to happen is it's going to do the VLOOKUP twice; it's going to do the VLOOKUP first and if it's an NA, it puts nothing like we have right here; if it's not an NA then it goes ahead and does the VLOOKUP.
Well, they improve this dramatically in Excel 2007.
Here's what we can do: We have the same formula in Excel 2007, we don't have to have Excel do the VLOOKUP twice; we use a brand new function that they added to Excel 2007 called IFERROR.
Okay, now I know we've had ISERROR for a long time, but this is brand-new-- called IFERROR.
Basically, we have our VLOOKUP as the first argument and then a comma, and here, what to do if we get any kind of an error from that first formula.
So quote quote, and what we'll see is this NA down here in row 19.
When I copy this formula down it will change to a blank.
IFERROR, much faster and much easier way to get rid of those NAs in Excel 2007.
Now, of course, the obvious problem is, if you use this function you can only send this workbook to other people that have Excel 2007; otherwise, the old Excel will return a name error-- doesn't know anything about IFERROR.
So, great question from Chris-- a couple of ways to solve, whether you're in Excel 2003 or Excel 2007.
I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel.
[ music ]
