Excel 2016 Vlookup function - Tutorial for beginners - how to use vlookup in microsoft excel 2016

and one use the popular the lookup function in Excel 16 recording this in Excel 2016 but it also working except 2013 and Excel 2010 and Excel 2007 the exact same way so I want to say to him you look up today now in the first one here we have two different sheets knows how she one has a list of all the customers and a customer idea isn't the first column as we can see that I'm gonna she to notice how she too has the orders that the customers placed here we have the customer I D but I like to show the company name from their customer right here in this year as well so what we really have to do is take that customer day from column B look it up on she won and find the appropriate customer I D and from their return the customer in that is a perfect use the vlookup so first of all I want to add a new column here when I right click on column C and insert now made up your company as for the economy now this point I want to start the vlookup formula so that you can sign but right now I'm gonna pick on the FXO you can type in the form of that were you can use the the function builder either way is fine show up in the effects now the vlookup to be the referral by tabling in vlookup here or there is also on the lookup and reference category so I pick up look up the reference and then if we scroll down before you look up by the way because the lookup table is going to be verdicts running down the screen whereas sometimes that he was running across the screen and then we would use an H look up however the movie do another video but the H look up so I was gonna have four different parameters to lookup value is what you're trying to look up to in this case when I'm trying to look up is the customary the right there in cell b2 now the table array is that the lookup table that's where you're trying to look it up at so that could be on the same she or in our case is on a different sheet so I wanna take resist him already and then I'll pick on she wanted the bottom so on a highlight from to all the way over to E that's 1894 go very very far down united to in this case so I thought about this a lookup table they could be on the same sheet as the other information could be on a different she like it is now they have many many columns across and many many rows down now the most important part about the table is the first column first column is one has to match so I was trying to match the customer I D to UCF the first column has customer IDs if you're trying to match your numbers than the 1st column of a table have been a number so that first comment really important and so you can see that the table Rays gonna be on that she 1823 92 now the column index numbers when the people get confused with so what you do is on the table you can from the left so in the table this column 1 column two three four and five Ric account for the laughs from the first column one two three four and five I want the company name right here is the second column right 12 so for that reason of type the number two there so that really means when I find the appropriate row I want the second column which is going to the company name now you see where it says range look up let's see what this has done here range lookup is a logical value to either true or false to find the closest match in the first column then you put true there were a few minutes then it's the same thing as being true or the final exact match will type in false now for this one type and false quite frankly probably like seven Atta Tanner it at a ten times you gonna make this false as you want an exact match in a in a little while and the same video I'll show you when where it is true there were doesn't have to be an exact match but also mister time you put the word foster so the lookup values what I'm trying to look up to in this case the customer I D the table array is where the lookup table is in this case is on she 182 through united to the most important part of the table is to first column that's what has to match column index number means go back there for a second County from the left column 1 column two if I wanted something from this column number three right who this case have column index number two which means the second column right there and the range lookup service which reads were gonna do an exact match so I'll clicking oK now let's see what gives us so look at the formal up here double click on that selfish aid formula equals vlookup open parenthesis of course the parts of the form below separated by a comma de tu cama she won exclamation point aides who threw you need to tell us the lookup table comma to the column index number just second column comma false stories you might do an exact match so when I hit the enter key there so I want to make that better mean there so it's as easy as a customer I D and it gave us this company they now let's see if that's correct go back to ship one and a quarter so the scroll down there is Danny T and you can see the other she has the appropriate company name Esther customer I D so look like it worked now we're going to copy that all the way down now here's something very very important to know when we copy this town we want to make sure that the second parameter here is one of the absolute references so in other words the table is always going to change so let me show you if i dont use the dollar signs in the form of the so right now as a 292 on the other she right so I just copied in the one cell and take a look at the second one the second one says a three through you 93 and then I even copied on the 30 1843 94th so it's getting further and further away from where the actual table is introduced in there would be it would make that sort of form that doesn't work right so copy that down we're gonna put the dollar signs in the form of which makes it one of those absolute references so i'm gona click on between a two part of that formula now really quick way to add the dollar science is the effort for function key and see how to put the dollar signs their pick of the East 292 prior an idea for their good so far seismic it what we call an absolute reference which reason we copied down that part of the former will still stay consistent so now let's go get let me get the black cross and I'll double black double click again go all the way down there you go so now I know how each one of them no matter how hard you down the Aussie 82 through unity see them because of the dollar signs you call that an absolute reference the first part of the form of his relative texts be four right there and then be five so there is a chance you're free so and that's what I want to happen however the second part of the four others too inconsistent 82 through united to they all say that because of the dark times you called that an absolute reference when the former doesn't change i mean when the fourth does she should be three is gonna go to be four and then be five that's a relative reference when it doesn't change we called that an absolute reference so take a look at the vlookup formula the B two is the saudi you trying to hook up which is right here she won a 232 is a lookup table applied almost always me that absolute when your foremost the number to me as I want to get the second column of information which is the which is the company name and the word false you want to an exact match I'm sure what that means now he has this to say vvv VV and I know there's not one that says that so it's a and a means not available or not found cause we're looking for an exact match because it would foster it didn't find an exact match so every turn that back to its original value they got right now that's given to another view that a new column now this time I look at the comp company the contact name from that other she almost very similar so I C equals will go back to the effects now because English is that then here's a look at the reference category and we'll go back to view all right the look of values still be to rate is still going to be the other sheep in this case we're going to go on a one through email before I think it was now I know that's going to be an absolute reference so I'm just gonna hit the F word function key right now my keyboard f4 and said that with the dollar signs in there for me so I know that was gonna be necessary now in this case let's go back to that table for a second you can account for the left to get the commonest number this column 1 2013 and for the range not copy of typing fast so very similar to the other formula except now she's column index three otherwise it was very much the same ok now I have the contact name is no customer I D and a copyright on down and now have a contact page for all of them have you look up is almost exactly the same except the column index number now certain number three now like to show you the other kind of you look up that does not require an exact match so in this case I'm trying to look up the score and converted to the letter grade you're in school you have other kids at school so now here's a look at him right now this time we don't have to do an exact match everything between 0 and 39 will give us an F so this kind of you I see you then go back to the effects of question always type in the US once you get out and there's a look up in category and now I want to show you something most recently used then the vlookup should be wrapped up to this touch the beer your list of favourite functions most recently used some more pics there okay now we're back to the screen look at this time will be 36 was with Shelby to the table array is when I haven't hello there in this case each to have sex now again I think just that every single time would make the second part of the vlookup into one of those absolute references so I know I need to get out and hit the F word function key and you can see it with the dark sides within that part of the form of the column index number this is column 1 column 1 column to sew a type of number two now the range look up I can either type of the road true there or I can leave that blank now that's an optional parameter because isn't like black so I just kind of screens when you see the ones that are going dark black those are required and one of their life are optional I can leave that blank the says if you leave it blank for a minute that it's true which is gonna find the closest match the closest match with outgoing members to its actually work ok now thats copy that down so the thirty-six got it got it because everything between 0 and 39 is going to give us an F when the the fourth part of the vlookup is true or it's not there then it's gonna do the closest match without going over closest match to the 36 in this table is really the 0 the closest without going over 20 so everything between Syria and 39 F if I had a false at the end of that if you look up then I would see a now but because it's black or because it says that were true that if I was the closest without going over and then I'll 7777 gave us a see everything between 70 and 79 will give us a sick so this is the other kind of you look up where we do not have to do an exact match many times you would use the other kind of you look at that we did before where does have to do an exact match but this gives you an idea of how to use both kind of yellow cups in Excel 2016