Learn Excel 97 Excel 2007 from Mr. Excel
The PodCast
Host: Bill Jelen
Hey welcome back to the Mr. Excel Net Cast. I’m Bill Jellen.
Basically, we start out with massive amounts of data to say how we’re going to analyze this. Well let’s fire up a pivot table, if you cold just solve this problem.
Hey welcome to the mystery excel net cast. 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 VLOOKUP so one thing you have to watch out is a result right down here the £NA that means that this item VG33-9 is not found in your table over here and the reason that NAs are so bad is that you can have 10,000 numbers and right in the middle a single NA to try to sum that up and your going to get NA so one single NA ill cause of all of down line formulas to go away.
So what do we have to do, well first thing I would do is I would come here and I will sort my VLOOKUPs by the Z to A buttons sort of the Z to A sort of descending that brings all of my NAs 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 gong 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 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, we could, I’m going to move here to the next sheet. We cold rewrite the formula and say hey we’re not going to point it L3 to M30 instead we’re simply going to point to all of columns L through M so I get rid of the 3 get rid of the $30 and we say going to go L to M, let’s shoot that down and then the nice thing you can just come here to the bottom of the table and see what BG33-9. We’ll copy that and come to the bottom of the table, paste, new item description there and you see there are the look up NA result goes away because this is looking at the whole column.
Now you say, “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 and M if you had some other table down there that’s gong to start to cause problems. The other option and this is what’s new in Excel 2007 is you got me here to your table and we can use Control T, Control T to find this as a table. My table has headers click okay, see that’s a nice little bit of formatting over there but then the nice thing is that we build the formula after that table has been defined so we come here and say it goes from L2 all the way down.
Then it’s going to refer to something called table two square brackets 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 then you just come to receive that new row becomes part of the table and here are and then it goes away instead of 3 different ways of dealing with NA error. I always just insert something new in the middle of the table a couple of different ways using the new Excel 2007, table functionality or just referring to the entire range of columns L through M that we can add things to the bottom.
Hey all right, VLOOKUP continuous tomorrow but thanks for stopping we’ll see you next time for another net cast fro Mr. Excel.
Transcription by:
Scribe4you Transcription Services