Learn Excel 97 through Excel 2007 from Mr. Excel
The PodCast
Hey welcome back to the Mr. Excel Net Cast, I’m Bill Jelen. Basically we start out with massive amounts of data as to how we’re going to analyze this well let’s fire up a Pivot Table if you could solve his problem.
Hey welcome back to the Mr. Excel net cast I’m Bill Jelen. Well the look up week continues we’re talking about VLOOKUPS this week. This time I have o do a VLOOKUP here in column B and I’m going to have to copy a VLOOKUP over the C, D and E to grab all 4 columns so the first I want to do equal VLOOKUP and when we point back to column A. You want for press that four; one, two, three times to lock down just the column A in that ways we copy this across it’s always going to look back to column A and then over here well choose our table so control shift down arrow shift right, right, right press F4 one single time to lock that table down and column 2, false.
Alright so we copy that across and you see that is always going to be grabbing column this is the painful step. We have to go through and edit these one at a time. Not bad for four of them but worst if you have monthly data or 36 columns or something like that. I go through and edit those. Alright so some things that I have seen, I’m going to go under the next worksheet. Sometimes we answered an extra little row up here when we put he numbers two, three, four, five, and we use equal VLOOKUP.
Again, back to the column A one, two, three, F4. Choose our range over here control shift down arrow press F4 one single and then for the column number we point up here in a B1 and we press F4 one, two times to freeze it down to row one. False and now that allows s to copy straight across and get different answers. Double click to handle to shoot it down alright so that’s another option. The other option which really, really, really is going to take a long time for excel to calculate is instead of using that B1 say we want the column of B1, column of B1 is going to return the number two and the nice thing about that is we copy it across that changes the column of C1 D1 and E1 will give us two, three, four, and five, alright so that’s how where to go.
The other way here is just to abandon the VLOOKUP completely. Alright so I’m going to answer the new column here. I’m going to call it where, where the where the column and here we’re going to use the match function, match, go find A126 in the first column of this table, press F4 and just, zero at the end that says we want an exact match just like the false does and it says hey that’s on row 26. Count the day and say what good is that when would ever want to know what row something is on? Well very interesting. we can then use a function called index, equal index, equal index so when the index of this date over here in column J and very interesting I want to lock the rose down but not the column because I want to be able to point to J K L and then as I copy across the index of that using this row number, the row number back here in column B and I will press F4 one to three times to lock it down. and I don’t need to specify the column number because there’s only one column so that gives our answer as I copied it automatically points to K then L then M great way to get all those answers in and this is fast, why because match only has to run ones index runs lightning fast we can actually hide for that column hide and we really don’t even need this extra rows they added up here. Great, great way to go using index and match one match for indexes definitely a faster way to go than having four cups.
Hey alright, well I want to thank you for stopping by we’ll see you next time for another net cast from Mr. Excel.
Transcription by:
Scribe4you Transcription Services