The most common error from your VLOOKUP formula is the #N/A error. Episode 1121 looks at how to find the #N/A and then three ways of adding those new values to your lookup table.
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, welcome back to the MrExcel netcast, I'm Bill Jelen. Basically, you start out with massive amounts of data, and say “How we're going to analyze this?” Well, let's fire up a Pivot table and see if we can solve this problem!
Hey, welcome back to the MrExcel netcast!
Well, it's VLOOKUP week. Yesterday we took a look at how to create a simple VLOOKUP formula to grab a item description from this table over here. And the big problem with VLOOKUPs, the one thing you have to watch out for is a result right down here: the #N/A, that means that this item BG33-9 is not found in your table over here. And the reason that #N/A’s are so bad, is that you could have 10000 numbers, and right in the middle a single #N/A. Try to sum that up and you're going to get #N/A, so one single #N/A will cause all the downline formulas to go away. So what do we have to do?
Well, first thing I always do is I come here, and I will sort my column of VLOOKUPs by the Z-A button, sort descending. That brings all of my #N/A's to the top. And then I have this brand new item BG33-9. Now, I want to add that item to the table, but I don't want to add it to the end of the table, because then I have to rewrite my formula. So what I'm going to do is, I'm going to copy two cells, including the cell that I want, and just go somewhere to the middle of the table, it doesn't matter where, and use Alt I E for Insert Copied Cells. I'm going to Shift cells down, BG33-9, and then type the new description, whatever it may be, and we're good to go.
Now a couple of alternatives to that, to inserting in the middle, I'm going to move here to the next sheet. We could rewrite the formula and say “Hey, we're not going to point to L3:M30.
Instead, we're simply going to point to all of columns L:M!” So I get rid of the 3, get rid of the $30, and we say we're going to go through L:M, let's shoot that down.
And then the nice thing is, you can just come here to the bottom of the table. Let's see what, BG33-9, will copy that, come to the bottom of the table, paste new item description there, and you see that our VLOOKUP #N/A result goes away, because this is looking at the whole column. Now you say “Well wait a second!
Aren't you looking at a million rows then?” And no, Excel is smart enough that it only looks down through the last row in the data.
Of course, now the one thing here is, you can't have anything else in columns L:M, if you had some other table down there, that's going to start to cause problems. The other option, and this is new in Excel 2007, is you come here to your table, and we can use Ctrl+T, Ctrl+T defines this as a table, “My table has headers”, click OK, see it as nice little bit of formatting over there.
But then the nice thing is, if we build the formula after that table’s been defined, so we come here and say that it goes from L2 all the way down, then it's going to refer to something called Table2[ #All ]! I want the second column, ,FALSE just like before, and we get our answer, shoot that down.
Now, the nice thing is, as I type new items at the bottom of the list, so BG33-9, and the new description, see, that new row becomes part of the table, and here our #N/A goes away. So three different ways of dealing with the #N/A error, I always just insert something new in the middle of the table. Couple of different ways, using the new Excel 2007 Table functionality, or just referring to the entire range of columns L:M, that we can add things to the bottom.
Hey alright, VLOOKUP week continues tomorrow, but thanks for stopping by, we'll see you next time for another netcast from MrExcel!
Well thanks for stopping by, we'll see you next time for another netcast from MrExcel!
Hey, welcome back to the MrExcel netcast!
Well, it's VLOOKUP week. Yesterday we took a look at how to create a simple VLOOKUP formula to grab a item description from this table over here. And the big problem with VLOOKUPs, the one thing you have to watch out for is a result right down here: the #N/A, that means that this item BG33-9 is not found in your table over here. And the reason that #N/A’s are so bad, is that you could have 10000 numbers, and right in the middle a single #N/A. Try to sum that up and you're going to get #N/A, so one single #N/A will cause all the downline formulas to go away. So what do we have to do?
Well, first thing I always do is I come here, and I will sort my column of VLOOKUPs by the Z-A button, sort descending. That brings all of my #N/A's to the top. And then I have this brand new item BG33-9. Now, I want to add that item to the table, but I don't want to add it to the end of the table, because then I have to rewrite my formula. So what I'm going to do is, I'm going to copy two cells, including the cell that I want, and just go somewhere to the middle of the table, it doesn't matter where, and use Alt I E for Insert Copied Cells. I'm going to Shift cells down, BG33-9, and then type the new description, whatever it may be, and we're good to go.
Now a couple of alternatives to that, to inserting in the middle, I'm going to move here to the next sheet. We could rewrite the formula and say “Hey, we're not going to point to L3:M30.
Instead, we're simply going to point to all of columns L:M!” So I get rid of the 3, get rid of the $30, and we say we're going to go through L:M, let's shoot that down.
And then the nice thing is, you can just come here to the bottom of the table. Let's see what, BG33-9, will copy that, come to the bottom of the table, paste new item description there, and you see that our VLOOKUP #N/A result goes away, because this is looking at the whole column. Now you say “Well wait a second!
Aren't you looking at a million rows then?” And no, Excel is smart enough that it only looks down through the last row in the data.
Of course, now the one thing here is, you can't have anything else in columns L:M, if you had some other table down there, that's going to start to cause problems. The other option, and this is new in Excel 2007, is you come here to your table, and we can use Ctrl+T, Ctrl+T defines this as a table, “My table has headers”, click OK, see it as nice little bit of formatting over there.
But then the nice thing is, if we build the formula after that table’s been defined, so we come here and say that it goes from L2 all the way down, then it's going to refer to something called Table2[ #All ]! I want the second column, ,FALSE just like before, and we get our answer, shoot that down.
Now, the nice thing is, as I type new items at the bottom of the list, so BG33-9, and the new description, see, that new row becomes part of the table, and here our #N/A goes away. So three different ways of dealing with the #N/A error, I always just insert something new in the middle of the table. Couple of different ways, using the new Excel 2007 Table functionality, or just referring to the entire range of columns L:M, that we can add things to the bottom.
Hey alright, VLOOKUP week continues tomorrow, but thanks for stopping by, we'll see you next time for another netcast from MrExcel!
Well thanks for stopping by, we'll see you next time for another netcast from MrExcel!
