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.
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.
Transcript of the video:
Learn Excel from MrExcel podcast, episode 2042 – IFERROR and IFNA!
I'll be podcasting all my tips from this book, click the “i” in the top-right hand corner to get to the entire playlist!
Alright, let's go back to the old days, Excel 2007 or before, if you needed to prevent the #N/A! from a VLOOKUP formula such as this one, we used to do this crazy thing. Scoop all of the characters of VLOOKUP mix up except for the =, Ctrl+C to put it on the clipboard, =IF(ISNA(, press the End key to get to the end, if it's #N/A!, then we say “Not Found”, comma, otherwise we do the VLOOKUP! Right, I paste it in there, and look how long that formula is, Ctrl+Enter, add a ) right there, Ctrl+Enter, alright look, that’s sweet.
Alright, but the problem is, while it solves the problem of the #N/A! every single formula is doing the VOOKUP twice. It doesn't wants us to say “Hey, is this an error? Oh, no, it's not an error. Let's go do it again!” So it takes twice as long to do all of these VLOOKUPs. In Excel 2010, they give us the awesome, awesome formula of IFERROR!
Now I know that sounds like what we had before, ISERROR or ISERR, but this is IFERROR. And the way that it works, take any formula that might return an error, and then you say =IFERROR, there's the formula, comma, what to do if it's an error, so “Not Found”. Alright now, beautiful thing about this is, let's say you had a thousand VLOOKUPs to do, and 980 of them work. For the 980 it just does the VLOOKUP, it’s not an error, it returns the answer, it never goes on to do the second VLOOKUP, that's the beauty about IFERROR.
Excel’s IFERROR came along in Excel 2010, but of course, the one really stupid problem here is that the table itself is returning an error. Right, someone had 0 quantity, Revenue/Quantity, and so we're getting a #DIV/0! error there, and that error is also going to be detected as an error by IFERROR. Alright, and it's going to look like the MrExcel is not found, he is found, it's just that whoever built this table didn't do a good job building this table.
Alright so, choice #1, Excel 2013 or newer, switch over to the function they added in 2013 that doesn't look for all errors, t only looks for #N/A! Ctrl+Enter, and now we'll get the Not Found for ExcelIsFun, but MrExcel is returning the #DIV/0! error. Really though, what we should be doing, is out here in this formula, we should be handling this formula to prevent that division by zero in the first place. So =IFERROR, this works for all kinds of things, press the End key to get to the end, comma, and then what to do? I would always put a zero here as the average price.
I had a manager once, Susanna(?), “That's not mathematically correct, you have to put in “n.m.” for not meaningful.” Exactly like that, it's crazy how long my manager just drove me crazy with their crazy request, sso, let's copy that down, Paste Special Formulas, Alt E S F. Now we get a “not meaningful” error here, the “Not Found” is found by the IFERROR, and the “not meaningful” is actually the result of the VLOOKUP. Alright, so a couple different ways to go, probably the safe thing to do here is to use IFNA, provided you have Excel 2013, and everyone you're sharing your workbook with has Excel 2013. This book, plus a whole bunch of others are in this book, is dripping with spicy tips, 200 pages, easy to read, full color awesome, awesome book. Check it out, click the “I” on the top-right hand corner, you can buy the book.
Alright, episode recap: Back in the old days we'd use a long format =IF(ISNA(the formula,0, otherwise the formula, the formula was calculated twice, which is horrible for VLOOKUPs. Starting in Excel 2010, =IFERROR, just put the formula once, comma, what to do if it's an error.
IFERROR, though, treats #DIV/0! errors the same as #N/A! errors, so starting Excel 2013 the IFNA function works just like IFERROR, but it will detect only the #N/A! errors.
This tip by the way, suggested by readers Justin Fishman, Stephen Gilmer, and Excel by Joe. Thanks to them for suggesting this, and thanks to you for stopping by, we'll see you next time for another netcast from MrExcel!
I'll be podcasting all my tips from this book, click the “i” in the top-right hand corner to get to the entire playlist!
Alright, let's go back to the old days, Excel 2007 or before, if you needed to prevent the #N/A! from a VLOOKUP formula such as this one, we used to do this crazy thing. Scoop all of the characters of VLOOKUP mix up except for the =, Ctrl+C to put it on the clipboard, =IF(ISNA(, press the End key to get to the end, if it's #N/A!, then we say “Not Found”, comma, otherwise we do the VLOOKUP! Right, I paste it in there, and look how long that formula is, Ctrl+Enter, add a ) right there, Ctrl+Enter, alright look, that’s sweet.
Alright, but the problem is, while it solves the problem of the #N/A! every single formula is doing the VOOKUP twice. It doesn't wants us to say “Hey, is this an error? Oh, no, it's not an error. Let's go do it again!” So it takes twice as long to do all of these VLOOKUPs. In Excel 2010, they give us the awesome, awesome formula of IFERROR!
Now I know that sounds like what we had before, ISERROR or ISERR, but this is IFERROR. And the way that it works, take any formula that might return an error, and then you say =IFERROR, there's the formula, comma, what to do if it's an error, so “Not Found”. Alright now, beautiful thing about this is, let's say you had a thousand VLOOKUPs to do, and 980 of them work. For the 980 it just does the VLOOKUP, it’s not an error, it returns the answer, it never goes on to do the second VLOOKUP, that's the beauty about IFERROR.
Excel’s IFERROR came along in Excel 2010, but of course, the one really stupid problem here is that the table itself is returning an error. Right, someone had 0 quantity, Revenue/Quantity, and so we're getting a #DIV/0! error there, and that error is also going to be detected as an error by IFERROR. Alright, and it's going to look like the MrExcel is not found, he is found, it's just that whoever built this table didn't do a good job building this table.
Alright so, choice #1, Excel 2013 or newer, switch over to the function they added in 2013 that doesn't look for all errors, t only looks for #N/A! Ctrl+Enter, and now we'll get the Not Found for ExcelIsFun, but MrExcel is returning the #DIV/0! error. Really though, what we should be doing, is out here in this formula, we should be handling this formula to prevent that division by zero in the first place. So =IFERROR, this works for all kinds of things, press the End key to get to the end, comma, and then what to do? I would always put a zero here as the average price.
I had a manager once, Susanna(?), “That's not mathematically correct, you have to put in “n.m.” for not meaningful.” Exactly like that, it's crazy how long my manager just drove me crazy with their crazy request, sso, let's copy that down, Paste Special Formulas, Alt E S F. Now we get a “not meaningful” error here, the “Not Found” is found by the IFERROR, and the “not meaningful” is actually the result of the VLOOKUP. Alright, so a couple different ways to go, probably the safe thing to do here is to use IFNA, provided you have Excel 2013, and everyone you're sharing your workbook with has Excel 2013. This book, plus a whole bunch of others are in this book, is dripping with spicy tips, 200 pages, easy to read, full color awesome, awesome book. Check it out, click the “I” on the top-right hand corner, you can buy the book.
Alright, episode recap: Back in the old days we'd use a long format =IF(ISNA(the formula,0, otherwise the formula, the formula was calculated twice, which is horrible for VLOOKUPs. Starting in Excel 2010, =IFERROR, just put the formula once, comma, what to do if it's an error.
IFERROR, though, treats #DIV/0! errors the same as #N/A! errors, so starting Excel 2013 the IFNA function works just like IFERROR, but it will detect only the #N/A! errors.
This tip by the way, suggested by readers Justin Fishman, Stephen Gilmer, and Excel by Joe. Thanks to them for suggesting this, and thanks to you for stopping by, we'll see you next time for another netcast from MrExcel!
