Using Excel Vlookup, Hlookup, Xlookup, Match and Index Functions

in this course we're going to talk about the different uses of vlookup hookup match and index and the new formulas that just came out recently X lookup and x match okay so all of these functions are going to be used to look up information from maybe one sheet to another sheet or maybe one table to another table and so on so let's take a look at the customer list sheet here this on the customer list the company name is the leftmost column and then I have the customer ID and the contact name and so on all right so let's go to the order list sheet and in the order list I have the customer ID so I want to take the customer ID from this sheet look it up on the other sheet and return information back to here well for a long time we would use vcup to do that now one of the problems with the vlookup is first of all it's a little bit complicated people would always get confused with it and second of all notice how I have the customer ID here and if I go to the customer list sheet the customer ID is in column B with the vlookup I was never able to look to the left of the column where I'm trying to match with the X lookup I'll be able to do that so let's try the classic V lookup okay so I want to go to this cell and uh now of course we can always use the FX to build these formulas uh in this case I'm just going to type them in when we type them in we'll start to see the syntax come up so if you do use the FX however right over here then all the functions I'm going to show you will be under the lookup and reference category all right so if I scroll down on the lookup and reference category of the insert function window there's index there's H lookup uh there's match which we're going to use today and then there's uh V lookup H lookup and x match so all of them would be under the lookup and reference category of the iner insert function window if you want to uh go that way which is no problem I'm just going to type them in today just so we can see the syntax that way so I'm going to type in the equal sign and I'll type in the lookup and then open the parenthesis okay so the first parameter is what we're trying to look up which in this case will be that customer ID in cell B2 all right that's what we're trying to look up then I'll type in a comma Now the next part is the table array in other words where are we trying to look it up from in this case the table array is on a different sheet so I'll pick on customer list now with the vlookup I have to highlight the column that we're going to match plus all the other columns that we want to use so I'm going to highlight column B and column C so now the column it has to match is going to be column B because that's the First Column of the table okay and then you have to go over as many tables uh as many columns that you want to include and we'll see what that means in just a second all right so the table array it says customer list exclamation point B col C when we see the excl uh exclamation point in a formula it means it's going to be a different sheet so it'll be the customer list sheet column B and column C and column B is the column that we have to match because that's the First Column of the table I'm going to type in a comma Now the column index number is where people get confused in the vlookup so you start with you don't start with the first column of the uh of the sheet you start with the First Column of the table so in the table this is column one and this is column two I want to return the contact name so I'll type in two there one two so you don't start from column A unless column A was the first column of the table you start with the First Column of the table so this is column one and then two so so that's why I have the number two here and then I'll type in a comma and then if you want to do an exact match we'll use false and if you want to do U an approximate match we'll do true which we'll do in some examples today as well so I'll double click on the word false and then close the parenthesis so it says equals vlookup open parenthesis B2 which is the um the customer ID from the other sheet comma customer list exclamation point B Co and c and the column that we're going to try to match is column B right because that's the First Column of the table col comma two means I want to get the second column from that table this is column one and this is column two so it's going to return the contact name and false means you want to do an exact match I'm going to press the NK and now we'll be back in the uh other sheet so let's uh let's think about what it just did it took v i n t looked it up on the other sheet found the right one and returned the contact name back to here now let's see if it found the right one you can see for v n it returned poll uh so let me go back to the customer list now these happen to be an alphabetical order so if I scroll down there's v n t and then you can see Paul is there with the V lookup the matching column always have to has to be to the left I can't go to the left of that with the V lookup but I will be able to go to the left of that with the h i mean with the X lookup which we'll get to pretty soon let's go back to the order list now I let to copy that down now here's a great shortcut that'll save you a lot of time with Excel so I'm going to click on that cell I'm going to get the black cross in the corner so that's called the speed fill or the autofill now of course you can drag the black cross but the shortcut is the double click on the black cross double click and it goes all the way down okay so now I have the contact name for each different customer ID notice how it says false up there which means we have to do an exact match so for example I'll type in a AA and now it says na a na a means not available or not found so uh because I have the word false in the v lookup then it didn't find an exact match there so it says na a now if I want that to say something different than na a then we have to use another formula we have to use something that's called uh is error so or if error so here's how I would correct that I'm going to go up to the formula I'll type in if error open parenthesis I'm going to come to the end of the whole thing and I'll type in comma open quotes not found and then close quotes close parenthesis so it says equals if error open parenthesis we had the entire V lookup comma and the word not found is in quotes because it's text closed parentheses so now when it doesn't find it it says not found you're going to see when we do the X lookup we can include the not found value right within the X lookup form so I wouldn't need another function when we use the X lookup let's go back over here and I'll type in v n and then it will find the appropriate value there so I can use the if error in general with any kind of formula uh if it does have an error that I can give it a better value so that's another good function that was included in today's session so now let's try to use the the new X lookup to find the contact name we're going to type this one in as well so I'll type in equals x lookup open parenthesis the lookup value is still the same that's going to be that customer ID in cell B2 and I'll type in a comma Now for the lookup array it's still going to be on the other sheet but now I'm just going to uh highlight the column I want to match which is column B so right away that's going to be easier than the vlookup with the V lookup I have to start there and drag across as many columns as I as I as I want to look at but here I'm just going to highlight the matching column which is column B I'll type in a comma Now the return array is what I want to return so I'm just going to highlight this column so I don't have to type in that number two or you know get really confusing I just I just highlight this column is the one I want to match and this column I want to return so I think they made it easier you know as you can see I'll type on a comma Now here look if not found remember with the V lookup I had to include a whole other formula here well here I can build it right in so if it's not sound not found I'll say not found and that's going to be in quoted because it's text comma and then here's the the match mode so we'll do an exact match now these two over here these other choices will be for when we do the true type of VL up in a couple minutes but this one's going to be an exact match good and then another comma Now the search mode uh on the V lookup I can only go from the top to the bottom with the um X lookup I can go from the bottom to the top when we say search last the first the significance of that is that with the vlookup let's say I was looking for this particular customer ID if it was in there more than once I would only ever find the first one but because with the X lookup I can search from the bottom I can find the other occurrences of that same customer ID or whatever you're looking for so that's a big uh that's a big factor as well in this case I don't have to type that in so I'll just type in another comma and then close the parenthesis actually those last two par uh parameters are actually optional you don't have to type in those commas but no problem so I really like the X look because here I'm just picking the column I want to match and then here I'm just going to return pick the column that I want to return I think they made that much more straightforward than the V lookup so when I hit the enter key then uh in this case let me get rid of those parameters there okay so you can see in this case uh we just have comma zero and then I thought that was a lot more straight for than the V lookup that's the whole point of the X lookup was to make that easier and I'm going to copy that down so it gives you the same results as the V look up but I just think it's easier to use because here I just have the column that I want to match and then I have the column that I want to return so I don't have to make it more complicated like the V lookup does now here's another great advantage of the X lookup with the V lookup I have the matching column and I have to go to the right of that there's there's no way I can go to the left of the matching column with the X lookup I can so watch remember how on the customer list sheet the company name is over here uh in column a right well watch what we're going to do I'll type in equals x lookup open parenthesis the lookup value is still going to be that customer ID right there in cell B2 comma the lookup AR we're going to go back to the customer list and I'll highlight column B again right that's still the column that I want to match and then another comma and now the return array I want to return column A I can actually go to the left of that now we can never do that with the V lookup I'll type in a comma so again if it's not found I'll say uh not found there good so that replaces the whole if error right there type in a comma and we'll do an exact match and that's really the last parameter that we need I'm going to close that parenthesis I'm going to press the nrk and then notice how we are able to return the company name back to here all right so if I go back to the vlookup you know I can I can try to put a negative number there so I'll try to put a a negative one you know it's just isn't going to let me do that right so or I try to put a zero or some other way to go to a negative number it's just is not going to let me do that so in this case I put it back to the number two good so that's a big advantage of the X lookup is we can actually go to the left of the column that we're going to try to match and I'm going to copy that down now if I come over here I'll type in a AA this one says not found because we had to do the if error right we had to do that whole extra function this one says not found because it's built right into the X lookup as you can see and this one says not found because it's built right into the X lookup so I really think they've made the X lookup better than the vlookup or easier to use for the reasons that I'm talking you about let's go back and put that back so it says v n t okay good now that that the vook up there was the word false which means it's going to be an exact match and for the X lookup I had the zero at the end which is the exact match there now let's try a vlookup and a x lookup where it doesn't have to be an exact match in this sheet I want to take uh the uh number score run it against this table and return the grade so this time I don't have to do an exact match uh anything between 0 and 39 will give us an F let's try it with the classic vlookup so I'll do equals vlookup open parenthesis the lookup value is going to be that 36 right there which is cell B2 and I'll type in a comma the table array is I'm going to highlight column F and column G so again with the V lookup I have to include the matching columns and the columns to the to the right that I want to include type in a comma the column index number again is going to be two you start from here this is column one and this is column two so I'll type in two there and then another comma Now this time I don't have to do an exact match so I'll double click in the word true and let's see what that does for us I'm going to close the parenthesis so the whole thing here says say equals V lookup open parentheses B2 comma F col G comma 2 comma true close parenthesis so it says F if that V had the word false at the end then it would say na here because 36 is not on the list but because we have the word true it's going to match the range so everything between 0 and 39 9 will give us an F all right so that's what the word true means when we use the vlookup I'm going to copy that down let's see how we would do that with the X lookup so I'll say equals x lookup open parenthesis to lookup value is still going to be that 36 and I'll type it which just s B2 I'll type in a comma Now remember for the X lookup for the lookup value I mean to look up array I just have to highlight that First Column okay which is f col F that's where I'm trying to match comma and now for the return array I'm just going to highlight this column over here so I don't have to worry about that one and the two and things like that I'll do another comma if not found uh well that one doesn't apply here because it's probably not going to be found uh I'm going to type in another comma Now the match mode means means that um I don't need to do an exact match so we're going to say exact match or the next smaller item so that's the same thing is saying uh the true in the vlookup or it could be the next larger item uh you know that's a new feature in the X lookup but usually we'll use that that first uh I mean this Choice here and here we could even do a wild card so I'll double click there it puts negative one there and then that'll be the end of the um of the X lookup so it says equals x lookup open parentheses B2 comma F col F comma G col G comma comma -1 so when you type it in at least it tells you what those parameters mean as you uh type that in and now here we have the F but the advantage I think is you know with the E with the V lookup I have to highlight the the matching column and as many columns as I need to go to the right and then I have to count from that column over and that's how I got to the number two with the xhookup I just think it's more straightforward here I'm just going to highlight the column that I want to match and then here I'm going to highlight the column that I want to return so I hope that you see that the xlup is just easier to work with I just think it's more straightforward uh I'm going to copy that one down I mean we get the same answers I just think it's easier to use the X lookup rather than um the vlookup so let's try another example like that I'm going to come over on this other sheet okay so this time I want to take that salary and run it against a table and return the tax rate so I'll type in 30,000 here and then here we have the vlookup let's take a look at that vlookup equals vlookup B2 is where the 30,000 is comma D2 through F7 so again um this is the column I want to match and I have to include these other columns D2 to F7 and then comma 3 means I when to return the third column when I find the match 1 two 3 which will return the tax rate notice how the fourth parameter isn't uh isn't there the fourth parameter is actually optional on both the V lookup and the H look up let me show you what I mean by that I'm going to pick up the FX over here so you see how these are in dark black that means that they're required and this one's in light black that means it's optional so let's see what that range lookup uh really says range lookup is a logical value that means it's going to be the word true or the word false to find the closest match in the column then you could put uh you could put true there like we did before or if you omit it like it is here then it means true and an exact match means false so if you put the word false there it'll be an exact match now some people also use zero zero would be an exact match as well if you ever see a zero in your vlookups um now you can put true there you can leave that last blank last one blank or you can put um sometimes you'll see a positive one there all right and then that means it's a true which means it's going to match the range so when it's blank like that it means you're true which means it's going to match the range so I'll click on okay so the 30,000 is between 273 and 585 everything between 273 and 585 is going to give us the 31% so that's how I do that with the the V lookup let's see how you do it with the X lookup so I'll do equals x lookup open parenthesis the lookup value will be that 30,000 which to cell B2 comma the look of array is just the First Column right I can just highlight these cells or highlight the entire column it's fine type in a comma Now the return array I'm just going to highlight these numbers over here right so I don't have to get involved with that one two three stuff or anything like that this is the column I'm matching this is the column I want to return nice and simple I'll type in a comma the not found here doesn't really apply CU you know we're to make it so it's not an exact match and here I'll say exact match or next smaller item like we did before all right and then I'm going to close that parenthesis so it says equals x lookup open parentheses B2 comma D2 col D7 comma F2 comma F7 comma comma -1 so it says 31 we'll just change that to a percent format as you can see all right and then it did find the exact same one so we can use the X lookup either to do that exact match like we saw with the V lookup or even to match the range uh let's go over here now the X lookup also replaces the H lookup now V means vertical like on this case the table is vertical it's going down the screen h means horizontal this means it's going across so let's see what we have here here's the salary and here's the hookup formula so I'll come over here and I'll type in 30,000 and now it says hookup h means horizontal so B2 is the set that we're trying to look up which is the 30,000 comma E1 through j3 so it's what I have in light blue over here E1 to j3 this time it's matching the first row whereas with the V lookup you're matching the First Column so it's almost like the table it's on its side and then the three means when I find the right one I'm going to go three rows down one two three and that's where we're getting the um the tax rate notice again that the fourth parameter is not there well let me go back to the FX and just like with the vlookup the fourth parameter is actually optional all right uh you can see it's optional because it's light black over here and the same kind of thing if you omit it it's the same thing as put in true there which means we're matching the range so I'll click on okay so the 30,000 gave us 31% everything between 273 and 585 will give us 31% that's how well I would do it with the H lookup now we can use the X lookup to replace the H lookup as well so I'll say equals x lookup open parenthesis the lookup value will still be that 30,000 type in a comma the lookup array is this time going to be that first row right so I'm just going to highlight from E1 J1 comma and then the world the return array will be the tax rates okay uh we're going to skip the if not not found uh parameter I'm going to go type in a comma again and then once again I'll use the exact match or the smaller item and it puts a negative one there and then close the parenthesis so I have equals x lookup open parenthesis B2 comma E1 col J1 comma E3 col j3 comma comma -1 and it gives us the same result and of course I'll make that into percent format so you don't have to worry about the different versions of the uh V lookup and the uh H lookup anymore all of those can replaced by the X Lookup All right so hope you're getting some good ideas about using the X lookup now another really popular way to look up things in another table is matching index so let's see how we can use matching index and then we'll use something new that's called x match so this time we have a two-dimensional table I want to find what row that we're looking in and I want to find what column that we're looking in okay so let's do it the classic way with a match and an index so I'll type these in as well say equals match open parentheses in this case the thing I'm going to try to match is that word July which is cell B1 and I'll type in a comma the lookup array is going to be over here in column D so I'm going to highlight from D1 to D15 and I'll type in a comma and then here is that same kind of thing uh you know usually we'll do an exact match here so I'll do an exact match but you can see there's the other choices as well double click on exact match and I'll close the parenthesis so I have equals match open parentheses B1 comma D1 D15 comma 0 close parenthesis so it gives the number eight Y8 starting here 1 2 3 4 5 6 7 8 so July is the eth route so why don't it just count the eight in the first place well that's created if we only have 15 what if we have, 1500 right we don't want to be counting those so now we found that July is the eighth row using the match function now I want to find out what column the word spraus is in so I'll type in equals match open parenthesis the lookup value this time will be the word sprockets which is B2 and I'll type in a comma this time for the lookup array I'll go from D1 over to H1 type in a comma and then the match type again is going to be the exact match which is a zero and I'll close the parenthesis so here it says equals match open parentheses B2 comma D1 col H1 comma 0 close parenthesis and I have the number three there why three 1 2 3 so we know that July is the eighth row and that sprocket is the third column then we'll use an index to find the intersection of those two so I'll say equals index open parenthesis now for the index you're going to highlight the entire array so I'm going to highlight from D1 to h15 and I'll type in a comma the row number is going to be that first match that we did which is going to be cell B4 and I'll type in a comma and the column number will be that second match that we did which will be B5 and then close the parenthesis so it says equals index open parentheses D1 colon h15 comma B4 comma B5 close parenthesis so we have 3337 let's see if that's correct July for sprockets is 3337 so I did a match to find the row that we're looking for I did a match to find the column that we're looking for and then the index is going to find the intersection of those two so if I come up here and if I type in August and then widgets notice how those numbers changed and now it says 3154 is that correct August for widgets is 3154 so usually we do a match to find the row that we're looking for and another match to find the a column and then an index will'll find the intersection of those two now remember how the remember how the classic V lookup was not able to go to the left it always had to go to the right of what we uh matched the index and the match can be used to actually go to the left of what we're looking for so let's we let's say we have the combined number so I know um it's 8832 so I want to try to find the month that goes along with that combined number so here I'm going to look at this column and be able to go to the left of that with the um with the V lookup I wasn't able to go to the left but with the match I actually am able to go to the left of that so I'll say equals match open parentheses the lookup value is B7 comma and I want to look at this column from H1 to h15 comma and then we'll find an exact match which is the zero and we'll close the parenthesis so it says equal match open parentheses B7 comma H1 col h15 comma 0 close parenthesis so it says six so now I know that 8832 is the sixth row down in column H right well then I'll use the uh I'll use the actually an index here to return the month so I'll say equals index open parentheses the array I'm just going to highlight this First Column D1 through D15 I'll type in a comma and I'll use that row number that we just did which is the number six which is cell B8 and it turns out the for um the other parameter here is optional when you type it in you can tell the optional parameters because they're in that square bracket like that see the the column number is in the square brackets which means it's optional I'm going to close the parentheses so it says equals index open parentheses D1 through D15 comma B8 close parenthesis so may is the sixth row let's see if that's uh correct starting here 1 2 3 4 5 6 right so with the matching index I can go to the left of what we're looking for whereas with the Vlog up I cannot do that now let's try it with um let's try something new that's called the x match so the x match excuse me the x match is going to be a little bit different than the match let's try so have an x equals x match open parenthesis the lookup value is still going to be that word August there which is cell B1 comma the look of array is still going to be D1 to D 15 comma Now with this one we can go the exact match again we can have that one where it say exact match or next smaller item and now we could do exact match or next larger item and now we have the Wild Card match so the regular match uh you know it with that zero negative one it wasn't as clear here they're making it more clear and now you can do a wild card match so that's a nice choice we'll do an exact match just going to put the zero there then I'll type in a comma and now again I can go from the top to the bottom or I can go from the bottom to the top the significance of that is the same as with the X lookup now you see if I have more than one match I wasn't able to get to that before but now I can by starting at the bottom and working uh you know finding the next one up so that's fine that's a nice new feature of the x match actually that last parameter is optional so I'm not going to get rid of the comma and close the parenthesis so you see the x match is similar to the regular match but added a couple new features there let's try it again I'll say equals x match open parenthesis here I want to match the word widgets which is B2 comma I'm going to go across this time D1 to H1 comma and again you have the exact match or the next smaller item or the next larger item or the wild card so the wild card is something new there we'll do another exact match and then again I can go from left to right or from right to left and that's that's a good little feature there and then uh even here we can make it if it's sort of ascending or descending and that's a new feature of the xmatch as well so I'll go from first to last and we'll close that parenthesis okay so it says equals x match open parentheses B2 comma D1 call H1 comma 0 comma 1 close parenthesis so now the um the index is still going to be the same I'll do equals index open parentheses for the array I'm going to go from D1 to H1 or uh D1 to h15 comma the row number will be that first x match that we did which is the number 10 which is really soell B10 comma and the column number will be that second one that we did which is b11 and then close the parenthesis I have equals index open parentheses D1 col h15 comma B10 comma b11 close parenthesis 3154 so is that correct 3154 so in this uh sheet with the the normal match then we did another match to find the column that we were looking for and we found the the index to find the intersection of those two then this one we were able to look over here and then go to the left of that so I typed in a number I was looking for from the combined column I use a match to find the row that that was in and then I found an index to found which month that that combined value was in right so with the match and index I can actually go to the left of what we were looking for whereas with the vlookup we can't and then we did it with the x match and you can you know there was a couple new benefits of the x match compared to the normal match and then here's another uh x match and then here's another index so hopefully you got an idea of how to look up information on other sheets with v lookup H lookup X lookup match index and even the uh the x match along with the index okay now let's talk about linking our sheets together so let's go over to sheet 2014 and we can see how it has the company budget for 2014 so then we have sheet 2015 in 2015 I want to show the total is from 2014 let me show you a couple ways to link your sheets together so I'm going to come over back to 2014 there's a couple ways we can do it I'm going to start on sheet 2014 and highlight these numbers across and uh I'm just going to do a copy there so I'm going to right click and pick on copy and it's going to tell you what it's about to be copied then I'll go over to sheet 2015 here I'm not going to do a paste I'll do a paste special and you'll see why so I'm going to right click in that cell I'll click on paste special and then we're going to do paste link okay so we did paste special and then paste link we have the same numbers from sheet 2014 but look at the formula it says 2014 exclamation point C11 which means you know sheet 2014 cell C11 so if C11 changes on sheet 2014 it's going to change here here the sheets are now linked together what we did is we did a copy from one sheet we came over to the other sheet and we said paste special and then paste link you can see how that says 105,000 let's see if it's going to change so I'll go back over here now if you have those blinking cells just press the Escape key to put that away I'm going to come over here and I'll type in 20,000 and notice how that new number says 111,000 here I'll type in 22,000 and now the new number on sheet 2014 is 108,000 if I go back to sheet 2015 this one says 108,000 118,000 so we copied from one sheet we came over to the other sheet and we said paste special and then paste link by the way that'll also work between two different workbooks I can copy from one workbook go over to the other workbook and then say paste special and then paste link and then the two workbooks will be linked together as well let me show you another way to link the sheets together so over here I'll type in 2014 grand total so remember now I'm in sheet 2015 so this time I going to start on sheet 2015 and then as I build the formula I'm going to go back to a cell on sheet 2014 so I'm going to type in the equal sign here now watch as I build my formula I'm going to go back to sheet 2014 and then I'll pick in that grand total over here which is cell j11 now I can go further with that formula but this will make the point that I'm trying to make when I press the enter key right now we'll see that same exact number on sheet 2015 so you can see how it says 1, 119,000 48618 when I press the nrk we see that same exact number over here in sheet 2015 look at the formula again it has the sheet name 2014 with the exclamation point and then j11 so that really means uh if that if j11 changes in that other sheet it's going to change here as well so we start the formula on sheet 2015 with the equal sign and then as we were building the formula we clicked on cells on the other sheet and then um that means if that other sheet changes then this will also change the sheets are now linked together let's see if that's the case let me go back over here so I'll type in 25,000 here now actually this total will change all these other totals will change and the grand total will change on the spreadsheet so now this number says 123,000 and this number says 1, 166,00 $223 if I go back to sheet 2015 then it changed here as well so I just showed you two ways to link your sheets together and that's how you can link your workbooks together as well all right so uh those are you know now we're starting to build the more complicated workbooks let me show you another feature here see how 2014 is laid out in 2015 and 2016 I would like to add all three of those sheets together and that's going to be a feature that's called consolidate so I just clicked on the consolidate sheet notice how the consolidate sheet is blank right now well pretty soon we're going to add those other three sheets together into this sheet so on the consolidate sheet I'll pick on cell A3 and then here is how we add the sheets together it is called consolidate so I'll pick in the data menu data and then I'm going to come over here and look for this icon it'll say that we're consolidate those three boxes so I'm going to click on that good usually when I do this I do a sum but it's nice to know the other uh functions are available if I ever need this so I pick on sum now I'm going to pick on the space where it says reference and I'm going to highlight the first sheet that I want to add up so I'm going to pick on sheet 2014 then I'm going to highlight a very specific range I'm going to go from A3 over to I9 okay A3 to I9 so if you notice my first row contains my uh column headers and My First Column contains my row headers that's going to be important so I'll pick in the word add here and then you see how that's now in the all references area then I'll pick on sheet 2015 notice how it wants to use use the same uh range which in this case is correct because they're L out they're lit out in a similar way now if those sheets were L out in a different way then we can highlight different she a different range there but these ones are going to be the same so I'll pick on ADD and now they're both in the all references area then I'll pick on sheet 2016 at the bottom notice once again it wants to use the same range which in this case is correct because they're laid out in the same way so I'll pick on ADD again and now they're all in the all references area you can keep on doing that for as many sheets as you want to add together and you would pick an add after each one and you can have as many as you wanted to now if you notice the labels are on the top row and the labels are on the left column the way I highlight the data before so I'll pick on top row and left column and then also I'm going to check on this box create links to the source data right there's that word link again so that means if those other sheets change this this one's also going to update which is a good thing so the way I got to this window was I pied in the data menu and I picked in the consolidate over here usually I'll do a sum but it's nice to know the other ones are available if I need those then I put in the space where it says reference and we highlight the first sheet that we wanted to add up and we picked on ADD and then we picked on each sheet after that that we wanted to add up and you pick on ADD after each one and you can have as many as you wanted to and then usually we pick on these three choices over here when that applies I'm going to click on okay now we're back in the Consolidated sheet this is now the sum of the three sheets I'm going to make column a bigger so we can see what that says there good so the 55,000 represents the sum of the three sheets for January or in sales now if you follow my mouse over here to the left notice how that formula says sum C4 through C6 well if I pick on that plus sign to the left of number seven then I can see row 45 and six so this number actually comes from sheet 2014 you can see that's the same kind of form that we saw before this number comes from she 2015 this one comes from from she 2016 so there's the link to the other sheets like we asked for on that window right so if those other numbers change these will change which will then change the sum here so that's that link that we talked about on the consolidate window now'll put that away again I can pick pick on this minus sign so I can expand those sections with the plus sign and put them away with the minus sign or I can expand them all with that number two up there and class them all with the number one that's if feature that's called a group and that happen automatically when we did the consolidate so the consolidate allows us to add uh sheets together that are laid out in a similar way all right very very powerful features here so today we did the V lookup and the uh H lookup and the X lookup we did match and index and x match and different different variations of those and then I showed you how to link the sheets together with uh different formulas and then we did the consolidate where we can uh add sheets together so I hope that you found today's course to be helpful where I showed you many ways to look up information on different tables or how to link your data together