VLOOKUP Tutorial for Excel - Everything You Need To Know

hello there and welcome to excel campus my name is John Acampora and in this video I'm going to explain everything you need to know to get started with vlookup so by the end of the video you're going to be writing your own vlookup formulas and you'll also know what causes some of those common errors that can be so frustrating and how to prevent them. Now I've designed this video to be a follow along lesson you can download the excel file that I use during the video there'll be a link in the description below the video there we can download the file and follow along and practice you don't have to do this but I highly encourage it it'll really help you learn faster so go ahead and pause the video now and download that file if you'd like and we'll jump over to excel and get started alright so I'm here in the practice file on the overview sheet and here's a list of the things we're going to learn today so I'm first going to give a definition and show some common uses for vlookup you can then follow along and we'll have a look at how to write a vlookup formula I'll explain the two main causes of errors for vlookups and how we can fix those I'll also explain why vlookup stops at the first match and the sorting myth as well and then we'll look at how we can create relationships between tables with a vlookup formula and this video will definitely be packed with tips and shortcuts to help make this process faster and easier so let's go over to the vlookup definition sheet and here I have a simple definition of vlookup and the whole point the purpose of vlookup is to look for a value in a column and return a result from a cell in a row where a match is found and this allows us to answer questions about our data so I have this menu example here this is a popular example I use our analogy for vlookup with this Starbucks menu if you've ever ordered food off of any menu then you've actually done a vlookup in your head and what I mean by that is the whole point of vlookup is to scan down a column until it finds an item let's say we want to find the price of the cafe mocha and then once we find that item we go over to the right to find the price and a different column and that's exactly what vlookup does and it always looks to the right and of course we'll learn more about this as we go but that's just one simple analogy to help you remember what a vlookup does and within excel there's tons of uses for vlookup it's of very powerful tool first of all like I said we can create relationships between data and look-up tables like we have here for pulling in data from some look-up tables we can use vlookup for that we can also use it to search databases and tables to return information based on a name that we're looking up we can use it for interactive reports and financial models maybe we have a drop-down here where we're changing the input and then that's returning different results so we can use vlookup to drive that and we can also use vlookup for tax and Commission rate calculations so instead of using some complex nested-if formula like we have here we can use a simple vlookup to look up values within a table like this and return a result so there definitely a lot of uses for vlookup even more than I've explained here but let's jump over to the right a vlookup sheet and write our first vlookup formula so I'm here on the right a vlookup sheet and we're going to write a vlookup formula and we want to answer a question which is what is the price of a cafe mocha sized Grande now we have our Starbucks menu up here and of course we could just scan down that column a until we find cafe mocha and then scan over to the right to the grande paalam to see this price of 395 and that's exactly what we want to return here in this cell and sell B 15 with a vlookup formula so go ahead and select cell B 15 I'm going to type equals and then the word vlookup and as you start typing vlookup you'll see it narrowed down here in the drop down we can just hit tab on the keyboard to autofill that and then we'll see the four arguments for the vlookup formula so we have the lookup value the table array the column index number and range lookup now this is the first time you're seeing vlookup those can be a bit confusing so I've also provided a simple definition here of those arguments and the first one is the value that we want to look for so in this case we're just going to select this cell here that contains the word cafe mocha so that'll put a reference to cell a 15 in our formula and then we'll go ahead and type a comma and the next argument is the table array so this is the range that we want to look in so we're going to select our entire range or menu here in this case which would be from cell a4 all the way to D 11 and that will create the reference here in our formula now one tip at this point right here is to hit f4 on the keyboard and that will create an absolute reference so if we were to copy this formula down our table array range will not move now this is not required and I'll talk more about this when we talk about errors but it's just a good habit to get into if you're using a laptop or a smaller keyboard you might need to hold the function key when you press the f4 key so so far we have the first two arguments and it's important to point out that vlookup is always going to look in the first column of the table array so we're looking for this value and cell a 15 and vlookup will always just look in this first and look down this first column vertically and that's what the V and vlookup stands for is vertical so it's going to look vertically down this first column here it's not actually going to look for that value in any of the other columns the reason we select those other columns is because we're now going to specify the column that we want to return a value from and that's the column index number or the column number of the value to return so in this case here we want a Grande which is the third column the lookup starts here this would be column 1 of our table array tall would be column 2 grande is column 3 and venti is column 4 so we want column 3 so we're going to type a comma and then we'll just type the number 3 here for the third argument and then we'll go ahead and type another comma and the 4th argument is the range lookup now that's probably the most confusing of all of them but this is either an approximate or an exact match now 99% of the time when you're starting out with vlookup you're going to put false here for an exact match we can use it approximate matches when we're looking up numbers for Commission or tax rate calculations but for the most part we want to specify a false here so it's best to just get in the habit of typing false of course you can select this and tab into it to put a false here for the last argument and now that we have all four arguments go ahead and close the parentheses and then hit enter and that will do the lookup and return the price of 395 now if you do have the practice file open at this point I encourage you to pause the video and write out the formula if you haven't already this will of course help you practice and really learn how to write vlookups so I just want to take a quick break and ask a favor of you if you're enjoying this video please subscribe to our Channel there's a big red button below this video they can click to subscribe and also get notified when new videos are published and we also have a free weekly email newsletter that contains tips and tutorials just like this that will help you learn Excel so I'll put a link in the description below this video as well where you can get plugged in with that thanks again and now let's get back to the training so we're going to look at the two main causes of heirs with vlookups I'm here on the heirs sheet and we're getting going to do some V lookups on our menu and we're going to answer this question which is how much will this order cost for size grandes so we have an order here maybe from some co-workers and we're going to look up all of those values return the prices and then total it up but we might get some errors while we do this so let's go ahead and write a vlookup again right here in this cell B 16 equals vlookup will tab into that this is going to be our lookup value I'm going to type a comma here and then select our table array now I'm not going to make this an absolute reference this time like I said before you can hit the f4 key right here but I'm just going to pretend like I forgot that step I'll type a3 here for our column index number to return that third column comma and then false for an exact match close the parentheses there and we'll go ahead and hit enter so that'll return our price of 415 which is great but now if we were to copy this formula down we're going to get some errors and you can see right here we're getting a hit an air and cell b-17 if I hit f2 on the keyboard to jump into this and edit this formula we can see that our table array this range here has moved down it's moved down to row five from row five to row 12 and that happened when I copied the formula down and the lookup value cafe latte is outside of that range we can see it's up here in Row 4 so vlookup is not going to find this value because it's outside of the table all right so I'll hit escape now and that's really the cause of this n/a air for the most part is that vlookup can't find that lookup value so let's jump back up here to sell B 16 hit f2 again I'm going to select my table array so just select the text here you can also use a little screen the shortcut here if you click the screen tip that will also select this text right here and then we'll hit f4 on the keyboard that will create the absolute reference so now I'll hit enter again we have this formula and I'm going to copy it down and now we get a result here hit f2 here we can now see that we have the absolute reference this range is anchored down and it's not moving as I copy the formula down so when you're starting out that's probably the most common cause for errors is you just forget to make this an absolute reference the table array an absolute reference copy the formula down and you're going to get some errors now another common cause of errors is that the lookup value doesn't exist in the table array and that's exactly what's happening here one of our co-workers decided to order a light beer from Starbucks might be a little too early for that so unfortunately this is not going to return a result here because light beer is not in this list it's nowhere in this list so vlookup is going to return in air now we can't handle those errors with the if air function and I'll talk about that in a separate video but to fix this for now let's just change this to an item that is in the menu so maybe that will give them a caramel macchiato instead just going to hit ctrl C to copy that and then ctrl V to paste it right here and now you can see we're getting the result of the price for the size grande and then finally down here we're still getting an air for this cafe mocha and of course cafe mocha this term or this word is in our table array so why is this happening well another common reason is that there's a space at the end of this phrase so if I just double click into the cell or hit f2 you can see the text cursor blinking right there and there's actually an extra space at the end of the phrase or the end of the text and that's causing this to not be an exact match so extra spaces in your text whether they're in the lookup formula here or in the table array you could have spaces at the end of these words as well that's going to cause a mismatch in the lookup value and return that air so of course we need to fix that there's many ways to do that but for right now I'm just going to delete that extra space and hit enter and now we can see we get our result right here so that should help you with the most common types of errors when you're starting out with vlookup because of course it can be frustrating when you get errors but that will help you resolve the majority of them so now we just want to get a quick total here we can select cell B 20 use the autosum function if we go to the Home tab of the ribbon and then we go right here to the autosum button we can just click that keyboard shortcut is alt equal sign hold down the Alt key and then press the equal sign that'll automatically give us this sum formula just hit enter and we'll get our result here we need $15.80 to take the starbucks to place this order so again I encourage you to pause at this point practice writing those formulas and fixing those airs so we're not going to look at how vlookup stops at the first match and the myth with sorting your data so I'm here on the first match and sorting sheet and I have the vlookup formula written here in cell b16 I'll just hit f2 on the keyboard to jump into this cell and the first thing that's important to know is that vlookup is always going to stop when it finds the first match so again here in this column it's looking for the word cafe mocha and column a and we can see now that there's actually two occurrences of cafe mocha however vlookup is always going to start at the top and look down vertically and stop whenever it finds the first match so it's going to stop here go over three columns and return the price of 395 it's never going to find this occurrence down here even though this has a cheaper price and things like that it's never going to find this because vlookup always stops at the first match and that kind of leads to my next point about sorting your data I commonly hear this myth that you always need to sort your data in ascending order before writing a vlookup formula or in order for a vlookup formula to work and that's not true at least not when the last argument is false for an exact match as we can see here our data is not in ascending order we have a W before a C and the formula is still working now if you are looking up numbers for Commission rate or tax calculations and you have the last argument as true in that case you would want to sort your data however like I said before especially when we're starting out with vlookup the majority of the time we're going to look for an exact match and have this last argument as false and in those cases you do not need to take the extra step of sorting your data now one caveat to that is if in this case here you did want to return the cheapest price you might want to sort these price columns in order for this row to be above this row here because vlookup will always stop at the first match however again it's not always required so just know that you don't always have to take that extra step to sort your data so we're now going to look at how to create relationships between tables and write V lookups to look up data onto other sheets so I'm here on the relationships tab and we have some sales data here in this sheet and we want to bring in some data from some look-up tables over here on this lookups look-up tables sheet so we have some look-up tables over here and we have a list of product names and the categories that those are grouped in here we have a list of salespeople and some information about the salespeople including the region they're in and over here we have customer IDs and customer names so we have some look-up tables over here I'll jump back over to the relationship sheet and we're going to use a vlookup formula to pull in that information so in this first example here we want to look up the product name and return the product category from those look-up tables so this will be some good practice for us we're just going to type vlookup tab into that our lookup value will be this product name here type a comma and then now we're going to jump over to the lookup tables sheet and we're just going to again select our lookup range I'm sorry our table array or a lookup range so we'll just select that there you can see now up here in the formula bar that I have the reference to the sheet name and then the range reference so right here again I can hit f4 on the keyboard to make that an absolute reference going to go ahead and type a comma and our column index number is going to be two for the second column so we'll type a two there you can type a comma and then type false for an exact match close the parentheses there and then when we go ahead and hit enter that'll take us back to the relationship sheet and show the result right here so now we can copy this formula down just select the cell double click the fill handle and that will copy our formula down now one thing you can do one little tip here is if you have a lot of data a lot of rows and you want to check for errors if you have the filters turned on you can just click the filter drop down menu here go to the bottom of the list and we can see we do have some n/a air values here in the sheet so this is a good way to go research those this uncheck select all check in a hit okay and that will filter down the rows for just our na airs and now we can see the reason here is that we don't have a product name in these cells we also have a dollar revenue of a dollar so this might be something weird that we need to go investigate to fill in a product name here or potentially we want to handle this with an if air statement and again we'll talk about that in a future video so I'm going to go ahead and clear the filter there we'll just leave those airs for now and we're going to do the same thing in this reps region column by writing the vlookup formula I'm going to explain another little tip here with a screen tip so again start type let's type writing the vlookup our lookup value this time is going to be our salesperson type a comma there I'm going to go over to our lookup tables sheet and now we're going to select this range here now when you're look up table has or your table array has a lot of columns sometimes it can be difficult to figure out which column index number we need to use however there's a little screen tip there that appears in the bottom right of our selection that says eight are by seven C and that 7c tells us that this is the seventh column that we've selected if we move over here we can see that changes to 6c for the sixth column so this lets us know that for our region we want to return these we want to specify the seventh column in order to return the region column so that's just a nice little tip there makes it really easy especially if you have dozens or even hundreds of columns within your data set and again I'll hit f4 to anchor that down then go up here type a comma we're going to type a seven here for that seventh column it's also important to point out that that is the seventh column of the range or the table right so that's a seventh column of our table right here it's not the seventh column of the sheet column K would not be the seventh column I believe would be the 11th column of the sheet but we want the seventh column of the table array that we have selected so that's important to note there that column index number is relative to the table right comma will type false for an exact match close the parentheses there and hit enter and that will return our region right here again I'll just double click the fill handle to copy that down quickly check to see if we have any errors we do not so everything looks good there and we've now created a relationship between this data table and the look-up tables and when I say relationship what I really mean is that this allows us to group our data so to bring in some metadata group our data and then if we were doing summary reports or pivot tables we could then quickly create a summary report based on the region so maybe some of sales by region we could create a report for that now that we've brought their region in here to our data table so this allows us to group data and kind of fill in the gaps of data that we do not have in our original data sets and I do have a whole nother video series on pivot tables and dashboards if you're interested in learning how to quickly summarize your data and create interactive charts and dashboards all right so I hope this video has helped to get started with vlookup I know it can feel scary and overwhelming at first but I highly encourage you to practice even go back and re-watch this video and it will definitely get easier over time now to help you learn even more I've prepared a bonus challenge file for you this will help test your skills and learn even more techniques and uses for vlookup and I also have a bonus video that walks through some solutions and I share additional tips and tricks to learn vlookup so I'll put a link to that in the description below this video definitely download that and check that out now if you have any questions please feel free to leave a comment below this video and we'd also love to know what you're going to use vlookup for now that you know how to use it so leave a comment below with that answer as well thanks again for joining me and I'll see you in the next video have a great day bye

Channel: Excel Campus - Jon

Views: 1,433,571

Rating: 4.9532275 out of 5

Keywords: excel, excel campus, vlookup in excel, vlookup formula in excel, excel vlookup, vlookup formula, how to do vlookup, vlookup function, excel campus jon vlookup, learn vlookup formula for beginners in excel, vlookup tutorial for beginners, vlookup for dummies, excel lookup tutorial, vlookup with exact match, excel campus vlookup, excel vlookup tutorial, excel vlookup function, excel vlookup formula, excel tutorial vlookup, vlookup errors in excel, vlookup error

Id: d3BYVQ6xIE4

Length: 21min 50sec (1310 seconds)

Published: Thu Aug 15 2019