**Autofill Function**

Have you ever wondered how certain formulas and functions work behind the scenes? Let's take a closer look at one of my favorite features: the Autofill function. When you double-click on a cell with a formula, like the crosshair we'll be using later, it will automatically fill in all the way down to allow you to scroll through your data easily. But what if there are gaps in column A? The autofill function won't stop at those gaps; instead, it will only continue where the data is complete. To practice this feature on your own, try applying it to a range like Column C or D and use VLOOKUP to find last names or states.

**Match Function**

Now, let's talk about the match function, which isn't really concerned with the value of data but rather its location. The match function is kind of like playing Jeopardy; you tell it what the value is, and it tells you where that value is. To use this function, we say equals match (open parenthesis), followed by the lookup value - in our case, the order number - a comma, then the table range, which is again the entire table, another comma, and finally the type of match we want to perform.

The type of match can be negative 1, 0, or 1. Negative 1 means you'll choose the number that's closest above it if there are multiple values in column A. If you choose a positive one, it's the closest number below it. If you choose zero, it means you want an exact match. Also, be aware that your data should be sorted in a particular order to use certain types of matches; for example, if your data is sorted in descending order, you can't use the negative 1 option.

**Defining Range Names**

To use these functions effectively, we need to define range names for our data. Let's go back to our source data and select only the order numbers - we already have a range name for the entire table, but now we want one specifically for Column A. To do this, click on cell A5, press Ctrl + Shift + Down Arrow (or Command + Shift + Down Arrow on the Mac), and type in a new range name with underscores instead of spaces. Press Enter to save it.

**Using the Match Function**

Now that we have our range names defined, let's go back to the match worksheet and use the function. We'll say equals match (open parenthesis) followed by "order number" - the value we're looking for - a comma, then the lookup array with our range name (the entire table), another comma, and finally the type of match we want. If you press F3 to cycle through options, you can see which values are available.

To start, I'll choose the column header as my first option, but since it's not exactly what we're looking for, let me select the order number instead. I'll click close parenthesis and Enter; voilà! The function returns us to cell C5 with the value "Order 1013" - which is the correct position according to our manual count.

**Index Function**

Last but not least, let's talk about the index function, which deals with two dimensions: rows and columns. We tell it here's the row number (the order number) and column number (which is optional). The official documentation says that if you want to use the index function with a column range, you should have your data sorted in descending order; otherwise, you'll need to sort it in ascending order for the positive option. Since our data isn't currently sorted, let's leave the default option and choose zero.

To find the value at a specific cell using the index function, we say equals index (open parenthesis) followed by the table range or data range - again, the entire table - a comma, then the row number (the order number), another comma, and optionally the column number.

"WEBVTTKind: captionsLanguage: enhi welcome to tuts plus my name is bob flisser 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 in 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 hlookup 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 whereas 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 tets 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 two 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 as 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 required arguments in one optional argument the first argument is what's called the lookup value and that's that unique identifier that we were 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 eighth 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 okay now there are times when an approximation is all right 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 then 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 can 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 what 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 press ctrl shift end if you're using a mac you want to press command shift end and you see that select all the way down and across to the last cell now to apply the name i click up here in the name box now not on the drop down but on the actual box here and when you do that you see that cell reference gets highlighted and i'm just going to call it data you could call it almost anything you want you can't have spaces or dashes in the name though and you're limited to 33 characters so i'll just call this data and press the enter key and there it is you could see that data is up there in the name box and we know that works because i can click somewhere over here and then when i click this down arrow and choose data you can see it's selected and again i did not include the column headers creating range names is a little different in google sheets so here i have the same exact worksheet and you can see i'm in my web browser so i'll click on the same first bit of data we can't select from a cell down and across in google sheets you have to select first across and then down or first down and then across so i'll press ctrl shift right arrow or if we are on the mac command shift right arrow and now i'll press ctrl shift down arrow or on the mac command shift down arrow now that i've done that i go up to the data menu and over here i choose named and protected ranges and then in this box over here i'll call it data and then click done and you can see it's over there and then i'm done with that i can close this little panel with that x so in this tutorial this is the only thing that's different between google sheets and excel okay so let's enter some formulas already enough talking right now i'm on the source data sheet let's click on the sale amounts sheet and you can see i already have the order numbers and what we want to do is use the order number to look up the sales amount like i was talking about so let's start entering the formula say equals vlookup and if you get that little syntax there you don't have to type the whole thing just press the tab key and it gives us a little bit of syntax help and the look of value we know is that order number so i click that and i type a comma now the table array that's the table we had so we don't have to go back to the source data sheet and select all that and make it an absolute reference because we already called it data so all i have to do is type the word data now if you did not create the range name you will have to go to the source data sheet and select the whole thing and make it an absolute reference also excel understands that oh yes i did create that range name so it's in here i'll show you also a little trick let me just backspace over that let's say i have a lot of ranged names or i created that range name a long time ago i don't remember what it is i could always press the f3 key on the keyboard and get the paste name box and just double click it and i'll type a comma and then it asks me for the column index number we said before that's column 8 that's where the sales figure is put in a comma and here it's even giving us a little syntax help do we want true or do we want false we want an exact match so you could type the word false or you could double click it and it puts it in close the parenthesis enter it and it's 40 and in fact if we go back here source data we can see oh yes indeed that's correct so let's go back here and we want to autofill this so click the cell and if you're not all that familiar with autofilling you notice that there's a little dot in the lower right corner of the cell when you put the mouse pointer over the dot the mouse pointer becomes this little crosshair and even if you know autofill here's something that most people don't know you don't have to click and drag this down for 200 whatever res all you have to do is double click the crosshair and it fills in all the way down so you can scroll all the way down and you can see it's filled in all the way that's because there are no gaps in column a if there was a gap somewhere in column a then that autofill will just kind of stop where that gap is if you want to practice this on your own you could go here maybe to column c or column d and go and use vlookup to find maybe people's last names or the states so let's take a look at the match function we can click the match tab here in the worksheet as i said earlier the match function isn't really concerned with the value of data but location of data actually it's kind of like the game of jeopardy you tell it what the value is and the match function tells you where the value is kind of if you think of maybe you're walking down the street and you say to somebody oh where is number 135 and they tell you oh number 135 is the fourth building down so let's talk a little bit about the syntax of the match function we say equals match open the parenthesis right just like any function and then you put in the lookup value that's just like what we were talking about before that's going to be the order number put in a comma then you have the table range table range is the same as what we were talking about before in our case we're going to use the entire table you put in a comma and then you enter the type now what's that type all about when you have that value you're looking for you may have a value that's just above it or just below it and to determine whether or not either of those are acceptable that type you can put in a negative 1 0 or a 1. negative 1 means you'll choose the number that's the closest above it if you choose a positive one it's the closest number below it if you choose zero it means you want an exact match also with the match function you have to be aware of whether your data are sorted in any particular order so you have to have data sorted in descending order to use the negative one option you have to have your data sorted in ascending order if you're going to use the positive one option and you can see that's the default or if you don't care if it's in one order or another then you can use a zero now since we're looking for only one piece of data we're looking for that order number we need to go and define another range name so let's go back to source data and we're going to be concerned just with the order number so we already have a range name that's for the entire table now we just want a range name for the data going down column a so kind of like what we did before you want to click on cell a5 press ctrl shift down arrow or command shift down arrow on the mac let's go up here click inside the name box and i'm going to call this order underscore number underscores are okay dashes are not spaces are not okay and press enter if you're doing this in google sheets use the same procedure that i showed you just before so let's go back to the match worksheet and we'll put in the function we'll say equals match open the parenthesis look at value we said that's the order number comma the lookup array right that's what we just put in the order number if you want you could press the f3 key and you can see there's the two range names double click type in a comma and we have our values in ascending order so i'm going to choose the one click it close parenthesis and enter it and now we can see that order 1013 is in the 13th position now if you had included the column headers then this would be showing 14. and we could eyeball this let's go back here and we can see there is order 1013 if we count from the top that is the 13th one down finally let's talk about the index function and i'm going to click on the index tab of this worksheet the index function is kind of like the opposite of the match function and it deals with two dimensions it deals with rows and columns so we tell it here's the row number and the column number i'm looking for which means a particular cell now return to me the value that's in that particular cell so let's take a brief look at the syntax of the index function we say equals index open the parenthesis first argument is the table range or data range again it's the entire table that we've been looking at put in a comma and then you have the row number put in a comma and then optionally the column number now this is a little weird because the official documentation tells us that column number is optionalhi welcome to tuts plus my name is bob flisser 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 in 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 hlookup 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 whereas 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 tets 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 two 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 as 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 required arguments in one optional argument the first argument is what's called the lookup value and that's that unique identifier that we were 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 eighth 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 okay now there are times when an approximation is all right 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 then 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 can 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 what 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 press ctrl shift end if you're using a mac you want to press command shift end and you see that select all the way down and across to the last cell now to apply the name i click up here in the name box now not on the drop down but on the actual box here and when you do that you see that cell reference gets highlighted and i'm just going to call it data you could call it almost anything you want you can't have spaces or dashes in the name though and you're limited to 33 characters so i'll just call this data and press the enter key and there it is you could see that data is up there in the name box and we know that works because i can click somewhere over here and then when i click this down arrow and choose data you can see it's selected and again i did not include the column headers creating range names is a little different in google sheets so here i have the same exact worksheet and you can see i'm in my web browser so i'll click on the same first bit of data we can't select from a cell down and across in google sheets you have to select first across and then down or first down and then across so i'll press ctrl shift right arrow or if we are on the mac command shift right arrow and now i'll press ctrl shift down arrow or on the mac command shift down arrow now that i've done that i go up to the data menu and over here i choose named and protected ranges and then in this box over here i'll call it data and then click done and you can see it's over there and then i'm done with that i can close this little panel with that x so in this tutorial this is the only thing that's different between google sheets and excel okay so let's enter some formulas already enough talking right now i'm on the source data sheet let's click on the sale amounts sheet and you can see i already have the order numbers and what we want to do is use the order number to look up the sales amount like i was talking about so let's start entering the formula say equals vlookup and if you get that little syntax there you don't have to type the whole thing just press the tab key and it gives us a little bit of syntax help and the look of value we know is that order number so i click that and i type a comma now the table array that's the table we had so we don't have to go back to the source data sheet and select all that and make it an absolute reference because we already called it data so all i have to do is type the word data now if you did not create the range name you will have to go to the source data sheet and select the whole thing and make it an absolute reference also excel understands that oh yes i did create that range name so it's in here i'll show you also a little trick let me just backspace over that let's say i have a lot of ranged names or i created that range name a long time ago i don't remember what it is i could always press the f3 key on the keyboard and get the paste name box and just double click it and i'll type a comma and then it asks me for the column index number we said before that's column 8 that's where the sales figure is put in a comma and here it's even giving us a little syntax help do we want true or do we want false we want an exact match so you could type the word false or you could double click it and it puts it in close the parenthesis enter it and it's 40 and in fact if we go back here source data we can see oh yes indeed that's correct so let's go back here and we want to autofill this so click the cell and if you're not all that familiar with autofilling you notice that there's a little dot in the lower right corner of the cell when you put the mouse pointer over the dot the mouse pointer becomes this little crosshair and even if you know autofill here's something that most people don't know you don't have to click and drag this down for 200 whatever res all you have to do is double click the crosshair and it fills in all the way down so you can scroll all the way down and you can see it's filled in all the way that's because there are no gaps in column a if there was a gap somewhere in column a then that autofill will just kind of stop where that gap is if you want to practice this on your own you could go here maybe to column c or column d and go and use vlookup to find maybe people's last names or the states so let's take a look at the match function we can click the match tab here in the worksheet as i said earlier the match function isn't really concerned with the value of data but location of data actually it's kind of like the game of jeopardy you tell it what the value is and the match function tells you where the value is kind of if you think of maybe you're walking down the street and you say to somebody oh where is number 135 and they tell you oh number 135 is the fourth building down so let's talk a little bit about the syntax of the match function we say equals match open the parenthesis right just like any function and then you put in the lookup value that's just like what we were talking about before that's going to be the order number put in a comma then you have the table range table range is the same as what we were talking about before in our case we're going to use the entire table you put in a comma and then you enter the type now what's that type all about when you have that value you're looking for you may have a value that's just above it or just below it and to determine whether or not either of those are acceptable that type you can put in a negative 1 0 or a 1. negative 1 means you'll choose the number that's the closest above it if you choose a positive one it's the closest number below it if you choose zero it means you want an exact match also with the match function you have to be aware of whether your data are sorted in any particular order so you have to have data sorted in descending order to use the negative one option you have to have your data sorted in ascending order if you're going to use the positive one option and you can see that's the default or if you don't care if it's in one order or another then you can use a zero now since we're looking for only one piece of data we're looking for that order number we need to go and define another range name so let's go back to source data and we're going to be concerned just with the order number so we already have a range name that's for the entire table now we just want a range name for the data going down column a so kind of like what we did before you want to click on cell a5 press ctrl shift down arrow or command shift down arrow on the mac let's go up here click inside the name box and i'm going to call this order underscore number underscores are okay dashes are not spaces are not okay and press enter if you're doing this in google sheets use the same procedure that i showed you just before so let's go back to the match worksheet and we'll put in the function we'll say equals match open the parenthesis look at value we said that's the order number comma the lookup array right that's what we just put in the order number if you want you could press the f3 key and you can see there's the two range names double click type in a comma and we have our values in ascending order so i'm going to choose the one click it close parenthesis and enter it and now we can see that order 1013 is in the 13th position now if you had included the column headers then this would be showing 14. and we could eyeball this let's go back here and we can see there is order 1013 if we count from the top that is the 13th one down finally let's talk about the index function and i'm going to click on the index tab of this worksheet the index function is kind of like the opposite of the match function and it deals with two dimensions it deals with rows and columns so we tell it here's the row number and the column number i'm looking for which means a particular cell now return to me the value that's in that particular cell so let's take a brief look at the syntax of the index function we say equals index open the parenthesis first argument is the table range or data range again it's the entire table that we've been looking at put in a comma and then you have the row number put in a comma and then optionally the column number now this is a little weird because the official documentation tells us that column number is optionalhi welcome to tuts plus my name is bob flisser 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 in 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 hlookup 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 whereas 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 tets 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 two 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 as 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 required arguments in one optional argument the first argument is what's called the lookup value and that's that unique identifier that we were 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 eighth 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 okay now there are times when an approximation is all right 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 then 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 can 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 what 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 press ctrl shift end if you're using a mac you want to press command shift end and you see that select all the way down and across to the last cell now to apply the name i click up here in the name box now not on the drop down but on the actual box here and when you do that you see that cell reference gets highlighted and i'm just going to call it data you could call it almost anything you want you can't have spaces or dashes in the name though and you're limited to 33 characters so i'll just call this data and press the enter key and there it is you could see that data is up there in the name box and we know that works because i can click somewhere over here and then when i click this down arrow and choose data you can see it's selected and again i did not include the column headers creating range names is a little different in google sheets so here i have the same exact worksheet and you can see i'm in my web browser so i'll click on the same first bit of data we can't select from a cell down and across in google sheets you have to select first across and then down or first down and then across so i'll press ctrl shift right arrow or if we are on the mac command shift right arrow and now i'll press ctrl shift down arrow or on the mac command shift down arrow now that i've done that i go up to the data menu and over here i choose named and protected ranges and then in this box over here i'll call it data and then click done and you can see it's over there and then i'm done with that i can close this little panel with that x so in this tutorial this is the only thing that's different between google sheets and excel okay so let's enter some formulas already enough talking right now i'm on the source data sheet let's click on the sale amounts sheet and you can see i already have the order numbers and what we want to do is use the order number to look up the sales amount like i was talking about so let's start entering the formula say equals vlookup and if you get that little syntax there you don't have to type the whole thing just press the tab key and it gives us a little bit of syntax help and the look of value we know is that order number so i click that and i type a comma now the table array that's the table we had so we don't have to go back to the source data sheet and select all that and make it an absolute reference because we already called it data so all i have to do is type the word data now if you did not create the range name you will have to go to the source data sheet and select the whole thing and make it an absolute reference also excel understands that oh yes i did create that range name so it's in here i'll show you also a little trick let me just backspace over that let's say i have a lot of ranged names or i created that range name a long time ago i don't remember what it is i could always press the f3 key on the keyboard and get the paste name box and just double click it and i'll type a comma and then it asks me for the column index number we said before that's column 8 that's where the sales figure is put in a comma and here it's even giving us a little syntax help do we want true or do we want false we want an exact match so you could type the word false or you could double click it and it puts it in close the parenthesis enter it and it's 40 and in fact if we go back here source data we can see oh yes indeed that's correct so let's go back here and we want to autofill this so click the cell and if you're not all that familiar with autofilling you notice that there's a little dot in the lower right corner of the cell when you put the mouse pointer over the dot the mouse pointer becomes this little crosshair and even if you know autofill here's something that most people don't know you don't have to click and drag this down for 200 whatever res all you have to do is double click the crosshair and it fills in all the way down so you can scroll all the way down and you can see it's filled in all the way that's because there are no gaps in column a if there was a gap somewhere in column a then that autofill will just kind of stop where that gap is if you want to practice this on your own you could go here maybe to column c or column d and go and use vlookup to find maybe people's last names or the states so let's take a look at the match function we can click the match tab here in the worksheet as i said earlier the match function isn't really concerned with the value of data but location of data actually it's kind of like the game of jeopardy you tell it what the value is and the match function tells you where the value is kind of if you think of maybe you're walking down the street and you say to somebody oh where is number 135 and they tell you oh number 135 is the fourth building down so let's talk a little bit about the syntax of the match function we say equals match open the parenthesis right just like any function and then you put in the lookup value that's just like what we were talking about before that's going to be the order number put in a comma then you have the table range table range is the same as what we were talking about before in our case we're going to use the entire table you put in a comma and then you enter the type now what's that type all about when you have that value you're looking for you may have a value that's just above it or just below it and to determine whether or not either of those are acceptable that type you can put in a negative 1 0 or a 1. negative 1 means you'll choose the number that's the closest above it if you choose a positive one it's the closest number below it if you choose zero it means you want an exact match also with the match function you have to be aware of whether your data are sorted in any particular order so you have to have data sorted in descending order to use the negative one option you have to have your data sorted in ascending order if you're going to use the positive one option and you can see that's the default or if you don't care if it's in one order or another then you can use a zero now since we're looking for only one piece of data we're looking for that order number we need to go and define another range name so let's go back to source data and we're going to be concerned just with the order number so we already have a range name that's for the entire table now we just want a range name for the data going down column a so kind of like what we did before you want to click on cell a5 press ctrl shift down arrow or command shift down arrow on the mac let's go up here click inside the name box and i'm going to call this order underscore number underscores are okay dashes are not spaces are not okay and press enter if you're doing this in google sheets use the same procedure that i showed you just before so let's go back to the match worksheet and we'll put in the function we'll say equals match open the parenthesis look at value we said that's the order number comma the lookup array right that's what we just put in the order number if you want you could press the f3 key and you can see there's the two range names double click type in a comma and we have our values in ascending order so i'm going to choose the one click it close parenthesis and enter it and now we can see that order 1013 is in the 13th position now if you had included the column headers then this would be showing 14. and we could eyeball this let's go back here and we can see there is order 1013 if we count from the top that is the 13th one down finally let's talk about the index function and i'm going to click on the index tab of this worksheet the index function is kind of like the opposite of the match function and it deals with two dimensions it deals with rows and columns so we tell it here's the row number and the column number i'm looking for which means a particular cell now return to me the value that's in that particular cell so let's take a brief look at the syntax of the index function we say equals index open the parenthesis first argument is the table range or data range again it's the entire table that we've been looking at put in a comma and then you have the row number put in a comma and then optionally the column number now this is a little weird because the official documentation tells us that column number is optional\n"