Video instructions and help with filling out and completing Who Form 2220 Dependents

Instructions and Help about Who Form 2220 Dependents

Hey welcome back to the mr. excel NetCast I'm bill jump basically you start out with massive amount of data how we're going to analyze this well let's fire up a pivot table and see if we solve this problem hey welcome back to the mr. Excel NetCast I'm Bill Gellin well vlookup week continues we're talking about vlookups this week this time I have to do a vlookup here in column B I'm going to have to copy the vlookup over to C D and E to grab all four columns so first thing we want to do equal vlookup and when we point back to column a1 press f4 one two three times to lock down just the column a that way as we copy this across it's always going to look back to column a and then over here we'll choose our table so control shift down arrow shift right right right right press f4 one single time to lock that table down and 1 column 2 comma false all right so we copy that across and you see that it is always going to be grabbing column 2 this is the pane full step we've go through and edit these one at a time not bad for four of them but worse if you have monthly data or 36 columns or something like that go through and edit those alright so some things that I've seen I'm going to go on to the next worksheet sometimes we insert an extra little row up here we put the numbers 2 3 4 5 and we use equal vlookup again back to the column a 1 2 3 F 4 choose our range over here control shift down arrow press f4 one single time and then for the column number we point up here to b1 and we press f4 one two times that reads it down to Row one false and now that allows us to copy straight across get the different answers double click the fill 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 comma V one 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 all right so that's a way to go the other way here is just to abandon vlookup completely alright so I'm going to insert a new column here I'm going to call it where where the where column and here we're going to use the match function match go find a 126 in the first column of this table press f4 and just comma 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 confident say well what good is that when when would we 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 rows down but not the column because I want to be able to point to jklm as I copy across the index of that using this row number the row number back here and column B and I'll press f4 one two 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 us our answer as I copied across it automatically points to K then L then M great way to get all those answers in and this is fast why because match when he has to run once index runs lightning-fast we can actually then hide format column hide and we really don't even need these extra rows that I 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 vlookups hey all right well i want to thank you for stopping viola see you next time for another net cast from mr. excel thanks for stop fire we'll see you next time for another deck gasps mystery seven you