Video instructions and help with filling out and completing When Form 2220 Index

Instructions and Help about When Form 2220 Index

Hi welcome to Tut's plus my name is Bob flu sir when you have a lot of data on a worksheet and you want to extract some of that data to use on other worksheets the vlookup match and index functions are great ways to do it these functions work in any version of Excel on Windows or Mac and they also work on the web in Google sheets and also on the web versions of Excel with the vlookup function and the V stands for vertical you have data arranged down columns and by the way there is also an H lookup function where you have data arranged across rows H is horizontal and it works the exact same way as vlookup so there's really no need for me to cover that in this tutorial you can grab actual data from a worksheet like you see here and use it in other sheets where as the match and index functions are really concerned with the positioning of data where your data are located rather than what is the actual value of the data if you want to follow along in this tutorial using your own workbook go right ahead if you'd like to use the sheet that I have here on screen you can see it's called vlookup example you could download this from the Tut's plus website right here on the page where you're watching this tutorial and before we actually start clicking around and writing formulas I want to give you a little bit of info on how vlookup works vlookup links to different tables and they could be on the same worksheet or more likely on different worksheets even in different workbooks using a unique identifier and the unique identifier that we're going to use in this tutorial is going to be an order number but in other situations it could be other things you can think of it sort of like is a serial number if you work with databases you might be familiar with the concept of a primary key so with this table in mind let me explain how the syntax works like any function we start off with an equal sign and then you have the name of the function vlookup and open a parenthesis there are three wired arguments in one optional argument the first argument is what's called the lookup value and that's that unique identifier that we are talking about and in our example that's going to be the order number now because you have to have commas separating the arguments in any function after you put in the lookup value you type in a comma and then the second argument is the table range and in our example that's going to be the entire table but keep in mind it doesn't always have to be that then we put in a comma and the third argument is the column number now let's say we want to get the sale amount and you can see here that's the last column in this table in this instance that's column number eight now don't get that confused with the fact that you can see here it's column H there's no law that says that the table has to start in column a so let's say for example if we start the table in column B then the sale column is still going to be the 8th column but it's going to be in column I instead of column H so that's why we're referring to the actual column number of the data table not the column of the worksheet anyway after you put in the column number you put in a comma and then that optional argument at the end is true or false and this could be a little confusing in this instance we're going to say false and what that means is is this false means don't give me an approximation give me an exact amount true means an approximation is OK now there are times when an approximation is alright but when we're looking at order numbers and when we want to plug in an order number and find the sale amount of that order number we really don't want an approximation and that of course we close the function with a matching parenthesis one other thing because we're going to put this entire table in a formula we need to make sure that we're referring to the table with an absolute reference because we're going to put in the vlookup function and then we want to autofill down that is we want to copy and paste down the formula all the way down a couple hundred rows understanding absolute references is beyond the scope of this tutorial but suffice to say it could be a little messy when you're using an absolute reference in a long formula so what I find it's easier to do is to create a range name when you give a name to a range like to this whole table that we have then you could use that name in your formulas first I'll show you how to create a range name in Excel and it works the same way in Windows or Mac and then I'll show you how to create a range name in Google sheets now the range itself you have a choice you can have it include the column headers or you could have it be just the data below the column headers and the written version of this tutorial I included the column headers so just to be a little different this time I'm going to include only the data and not the actual column headers so I'm going to do is this I'm going to click over here on cell a5 so that's the first cell of actual data and I want to select down and across to the last cell here so in Windows I'll