How to use VLOOKUP in Excel - Complete Tutorial

hey everyone kevin here today i want to show you how you can use vlookup in microsoft excel vlookup is one of the most popular functions in excel if there's a good function to know how to use this is a really good one to add to your toolbox in fact when i worked at microsoft this is a function that i used all the time so what is vlookup and what does it even stand for vlookup stands for vertical lookup and it allows you to look up information in a vertical list so let's take an example let's imagine that you have a customer id and you want to know the customer's name so you have a list with all the ids and the customer names you can look up that id and then get the customer name back or maybe you have two different tables of information and you want to bring them together you can use vlookup to join those two separate tables it's really versatile today we're going to start off by going in depth on how you use vlookup then i want to show you a variation of vlookup called hlookup where you can look up information in a horizontal list and then at the very end i'll save the best for last i want to show you how you can use something called x lookup which improves upon both vlookup and hlookup now if all of this sounds a little bit overwhelming and maybe a little bit confusing don't worry we're going to walk through all of this step by step and if you want to follow along i've also included a sample workbook in the description you can click on that and then you can follow along with this video all right well why don't we jump on the pc and let's start looking up here i am in microsoft excel now and once again if you want to follow along i've included a link to this workbook in the description of this video and in this workbook we're going to start all the way on the left hand side with this sheet called vlookup exact match we're going to work our way through here and by the end of this video you are going to be an expert in looking up using vlookup hlookup and also x lookup okay so let's get started with a really simple example that will demonstrate how you can use vlookup right here on this sheet i have a table of customer information over on the left hand side i have the customer id there are five different customers then you see the customer name and then there's some notes about the customer now over here i want to be able to type in a customer id so let's say i type in customer id number four once i type that in i want to look up this value over in this table and then i want to get the customer name back so this is just a really simple example and we're gonna dive in in just a moment but before we dive into the nitty-gritty of how you use vlookup i want to take a moment to talk about how you should organize your data over in the lookup table over on the left-hand side you want to make sure that the value that you're looking up is the leftmost column so here i'm looking up the customer id and over here in this table the customer id right now is already the leftmost column so if you have to rearrange your data so the lookup values over on the left feel free to do that before using vlookup along with making sure that the lookup value is in the leftmost column you also want to make sure that your lookup column is sorted in ascending order this helps vlookup make sure that it's finding the right value so here you see that i have one at the top and then it grows all the way down to five also if let's say you're looking up different names you want to make sure it's in alphabetical order starting with a all the way down to z so let's say that it's not in ascending order it's easy to change that you can go up to the header of your column you could right click on that and then you can go down to sort and here you could sort a to z also alternatively you can also go to the data tab up on top and here you can click on filter so here i'll toggle it off and i'll toggle back on and over here i can click on the filter and here too i could also sort from smallest to largest next you also want to make sure that there's a common field that you can use to make a connection so over here i'm looking up the customer id and this table also has a customer id so when i look up four i'll be able to find a match over here now i wouldn't want to look up say the address of the customer because this table doesn't have an address so i wouldn't be able to match on anything so that wouldn't really make any sense lastly it also helps tremendously when you do a vlookup to look up on a table of information and you don't necessarily have to make it a table but it makes it a lot easier and it'll also help you avoid some errors so you might be wondering well how do i turn data into a table well let's jump to this next sheet here called vlookup make table and i'll show you how you can make a table so right now this is organized in what looks like a table but it's not an official excel table and it's really easy to turn this into a table right here once i have all of the data selected you can go up to insert up here on the top tabs and here's an option to convert it into a table when i hover over you also see that the shortcut key is ctrl t so i could also press that to make it a table let's try the shortcut key i'll press ctrl t and here it says what's your table and so i've already selected it so here it identifies all of it and my table has headers so i'll make sure to check this box and then click on ok and look at that i now have a table of information so that worked exactly how i wanted it to now let's go back to the previous sheet we've gone through a bunch of information about how you should structure your data before running a vlookup and all that's very important because this ensures that when we run the vlookup we'll be running it correctly next i want to enter my vlookup function so over here for this cell where i want the name to appear i'll click in here and then let's go up to the formula bar and i'm going to click on this fx symbol let's click on that this opens up a prompt where we can enter in our function and we want to use vlookup so right up here type in vlookup and then click on go right here i see the function so let's double click on that this now opens up another prompt where i can enter in all of my function arguments and we're going to walk through these to help you understand what they mean so let me pull this down a little bit so we can see it and we can see all of our data up above first i need to enter a lookup value and this is bolded here meaning that it's a required argument that i need to enter and the lookup value well i want to look up customer id number four so i'm going to click over in this cell and that's now selected f2 that's my lookup value we're going to look up four right down below here i can verify that it's looking up for here it's showing me the value right here next it asks me to enter the table array and this is where i'm doing the lookup and over here i want to look up against this table so here i'll select the entire table and so one of the interesting things is since we defined this as a table here it refers to that table now once again i mentioned that it's a very good practice to turn your table array into a table first and you might be wondering well why is that well let's say maybe i added another customer the table would automatically account for that if instead i went through and let's say i just selected some cells if let's say i added another customer or another row it wouldn't account for it and then maybe my lookup wouldn't work properly so it's a good practice to create a table now you might also be wondering well instead of creating a table couldn't i just select all of these columns and then run a vlookup one downside with that is let's say you have content underneath your table those might also be included in the vlookup so once again as a best practice create a table and then you can just search across that table it tends to avoid errors in the long run i've now selected my table and next there's another argument here called the column index number so right now i'm looking for the value for in this table over here so it looks over in the first column and it says is there a number four and here it'll find the number 4. now this next argument says well which column do you then want to return so i've selected this table and it has 3 columns 1 2 3 and i want to send the name back and the name is the second column so right here i'll enter the number two and last there's something called range lookup and in a moment i'll go into more detail on what that means but for now let's set it to false we want this to be an exact match what i mean by that is when it finds customer id number four it'll look in this list and only if it finds the exact value for number four will it return the customer name let's say i sent in customer id 3.5 or 4.5 or customer id 6 well there is no exact match so that would return an error i want this to be an exact match so once again i'll enter false and a little later i'll show you when you might want to enter true for this now this all looks good and down below i can already see the output it looks like wholesome foods is customer id number four i'll click on ok and here i see the exact value i was expecting now because i have the vlookup set up here i can type in another customer id so i could type in customer id number one and here i see the associated customer i could also type in customer id number five and that'll show me the customer associated with number five so pretty cool stuff now we have vlookup working with the vlookup let's say that maybe i enter a customer id that doesn't exist so let's say i enter number six right here you can see there is no customer six when i press enter i get this n a error back and it doesn't really look that friendly now if you've done vlookups before you'll start to recognize that anytime you get this it simply means that it looked up for that exact value it didn't find a match and it returns this back but once again it's not that friendly so we can improve upon this to improve upon this we can use another function called if error if there's an error we can show something a little bit friendlier right up here on the formula bar let's click right after the equal sign and before the vlookup and let's type in if error so i'll type that in and then let's open the parentheses and right now there are two different arguments one of them is the value and one of them is the value if error so the first one is if there is no error what should it display and if there is no error well i just want to show the output of the vlookup so i'll leave the vlookup here but let's say there is an error i'll enter in a comma here and this is where i enter my second argument i could just put in some quotes and maybe i just say not found just so it's a little bit friendlier i'll close the quotes and then i'll close the parentheses once i'm done entering that in i'll hit enter and here now you can see that it looks a lot friendlier so if i type in customer 5 well we have a customer 5 so it shows me that customer name but here if i type in customer 6 now it says not found instead of n a so that looks a little bit nicer next i want to show you how closest match works in this example we were looking for an exact match but when would you use the closest match and for that let's go down to the different worksheets over here and click on the one called vlookup closest match on this next worksheet i have two different tables of information i have cookie orders over here so here i have an order id and then i have a certain number of cookies that that customer ordered and i want to offer some free cookies just as an incentive to order more so here for example if you order a hundred cookies we'll throw in five for free so you get five percent free cookies or if you order 400 cookies you get 20 free cookies so just as an incentive to drive more people to order cookies now i want to put in how many free cookies i should include in each order but i don't want to have to go through and say hey look at how many cookies they ordered and then figure out where that sits on this table and in fact when you look at this here's someone place an order for 26 cookies there is no 26 in this other table this is where we can use closest match another good example is let's say you're calculating taxes and you have different tax brackets that's another instance where you might want to use closest match just like we did before let's go over into cell c2 and we're going to enter in our vlookup formula once again let's go up to the formula bar and click on the fx this opens up insert function and right down here you should see vlookup as a recent function i'll click on this one once again we can enter in the different function arguments here i need to enter my lookup value and i want to look up how many cookies they ordered so if someone orders 26 well how many free cookies should we give so here the lookup value is 26. right down here i need to select the table array and i'm looking it up against this table over here so here i'll select this table right down here it says well what index or what column do you want to send back so here i'll look up 26 and here it's going to look it up against the leftmost column and when it finds out what bucket it falls in then i want to give back how many free cookies i should include and that's the second column so i'll enter a 2 here now with range lookup this time instead of doing an exact match i want to use a closest match and if i don't enter anything at all in this field it'll default to using closest match so i'll just leave it blank and then let's click on ok and look at that using the vlookup i now know how many free cookies i should include as each one of these orders and so you might be wondering well how does closest match work exactly so here let's just take a look at this example a customer ordered 26 cookies and that falls between zero and a hundred so it's greater than zero but it's less than a hundred so it finds the closest value that's less than n so the closest value to 26 is 0 and also 100 is close but it falls back to 0 because that's less than 26 so here it uses this value with 101 it's greater than 100 but it's less than 200 so it falls back to the closest value that's less than and so here in this case we include five cookies so there you can get a feel for how it works or even here's another example where it's 392. so it's between 3 and 400 it's closer to 400 but 400 is greater than it so it falls back to 300 and then we've included 15 free cookies now hopefully you're starting to get a feel for how vlookup works and the great thing is when you use vlookup your two tables don't have to be on the same sheet in fact you can have them on completely separate sheets let's jump over to the next sheet called vlookup across sheets and here's the same exact example except with this example i don't have the other table sitting right next to it instead the other table is on this second sheet so let's see how we could run the vlookup to get the same results here again i'll click into this cell let's click on fx up on the formula bar this opens up the insert function let's select vlookup right here the lookup value once again i'm looking up the cookies ordered next i need to type in the table array so i'll click over here and then let's jump to this next sheet and this is the table i want to look up against so i'll select that and here just like we did previously i want to return the second column and here with the range lookup i'll leave that blank so it'll be a closest match next let's click on ok and look at that the vlookup works just like before except this time it's working across different sheets next let's go down and let's click into hlookup to see how we can do a horizontal lookup now so far we've been doing vertical lookups our table is organized in a vertical list but what if you're looking up against a table that's organized horizontally we can use something called hlookup and it's the exact same concept as vlookup but we're using it against horizontal data so let's just test this out to see how it looks and right up here let's select this cell here and then let's go to the formula bar and click on the fx this once again opens up the insert function prompt and this time let's type in h lookup click on go and then select hlookup hopefully by now this prompt is starting to look very familiar first off we need to select the lookup value and once again i'm looking up how many cookies were ordered right down below this is now my table array that i'm looking up against i want to see how many they ordered and then i'll decide how many free cookies to get back so here i'll select table array and i'll select this table now the key difference here is this is now a horizontal table instead of a vertical table and that's fine it works just the same except we need to use a function called hlookup and over here it says the row index number so it's not a column index it's a row index so in this table over here we want to send back the second row so here i'll type in a two also here for range lookup i'll just leave it blank we're gonna do a closest match again and then let's click on ok now here once again we see that if a customer ordered 26 cookies well for 26 cookies that's between zero and a hundred so we give free we give zero free cookies back one of the things you'll notice though is i got this n a for all these other values so i don't think it's working quite right let's click back into the formula bar to see what's going on here for the table array i'm just looking at these cells these are relative cells here as i go down if i click into this cell you see that it automatically adjusts the table that it's looking against i don't want it to do that so instead i could go up to the top and i need to make it an absolute reference so as i pull this formula down it continues to look at this table to do that i can press the f4 key i can press the f4 key again and then i'll hit enter and now you see that the formula works properly that's one of the downsides of using a horizontal table in excel you can't properly define it as a table so you have to make sure that you use an absolute reference and i think most people when they organize data in excel they tend to use a vertical list horizontal lists aren't as popular but i did want to touch on how you can use hlookup as well depending on how your data is formatted and organized now by now your lookup skills should be getting pretty good but what if i told you there's an even better version of looking up and that's called x lookup it can do everything that vlookup and hlookup can do but it can do even more on its own it can search on vertical and horizontal lists so you don't need separate functions and it does a lot more than just that and in a moment we're going to run through to see what some of those additional benefits are and for that let's go down and click on the worksheet called x lookup i'm now on the x lookup worksheet and let's take a look at how this works over on the left hand side i have a table with a whole bunch of cookie orders i have an order id i have what product they ordered how many we sold the date and then i have a bunch of empty columns i have the revenue per cookie the cost per cookie and the order profit and i don't know what those are luckily though i have a nice reference table over here that tells me how much revenue per cookie how much cost for cookie and then here i have the cookie type and look at that i can match based on the cookie type so here i have the cookie name and here the product name matches with the cookie type exactly in fact if i click on this drop down i can see that all the cookie types are exactly the same so we could certainly use vlookup to fill this out but it's not going to work quite as well now first off one thing to notice is my cookie type is not the leftmost column in fact here it's the rightmost column so if i wanted to run a vlookup well first off i'd have to move the cookie type over onto the left hand side but why do that we can use x lookup and we don't have to rearrange our data so let's go over here and first off let's pull in the revenue per cookie for all these different cookie types and let's use x lookup just like we did before let's click on the fx within insert function let's type in x lookup and then click on go and here you should see the x lookup function let's click on that and then click on ok this once again opens up function arguments and at first glance you'll see that there's more that we can fill in but more isn't necessarily bad right here you see the three bolded values and these are the required values and down below you have some optional different settings that you can configure and so really x lookup is going to give you a lot more power and let's start with the just simplest example first i want to look up a value and just like we did with vlookup well our lookup value is over here we're going to look up chocolate chip and we want to look it up in this table so over here i need to select the lookup array now previously with vlookup we selected this entire table we don't have to do that anymore instead i'm just going to select this column i want to look up in this column to find that value so one of the nice things is once again i don't have to rearrange my data over here i want to select what i want to return so i'll click over here and i want to return the revenue per cookie so in this same table the revenue is over here so i'll select that column so i don't have to worry about entering in a column index or a row index instead i just select what i want to get back so it's a little bit more intuitive to use now some of the other benefits down here there's something called if not found remember earlier when we got that n a error and we used another function called iferror to make it a little friendlier but you don't have to worry about that anymore here you can simply type in something like let's say not found and remember this is optional so you don't necessarily have to do this but here it's just built directly in in a moment we'll come back to what match mode and search mode mean but for now we have our basic x lookup working so let's click on ok so look at that x lookup has now returned the revenue per cookie so it looks up here chocolate chip here it finds chocolate chip and it returns this value over to the left for five dollars so that's pretty cool you don't have to rearrange your data and here it basically does what vlookup does but it has a little bit more power next let's do the same thing just to make sure we really understand this let's do it again with cost per cookie here i'll click on the cell let's go back up to fx and once again let's click on x lookup this opens up the function arguments and over here i want to look up this product type and i want to look up over here so i want to find the cookie type over in this table next i want to return the cost so i'll select this column this is what i want to return back and then here if not found i'll just leave that for now i don't necessarily need that and i mentioned we'll come back to these other items in a little bit so all of this looks good let's click on ok and right there i see my cost per cookie it just automatically populates all of that right here okay so next i want to calculate the profit and to do this i'm going to combine multiple functions together and this is going to show you some even more power of x lookup so in this order profit cell right here let's enter in equals sum so to calculate the profit well i want to sum the revenue and the cost so basically my profit on an individual chocolate chip cookie is three dollars five minus two and then i want to multiply it by the units sold and i can do all of this in just one formula up here on the formula bar so first let's enter the sum and next i want to use x lookup so let's enter in x lookup and here i'll enter in the function now if we want to make it easy here we can click over on fx again and let's fill out the x lookup this once again opens up the function arguments and the lookup value well i want to look up chocolate chip so i'll select that over here i'll select the lookup array and it's this column again i want to look it up over here now for the return array one of the things that's really interesting is i can return multiple values and that's one of the powers of x lookup so here i want to return both the revenue and the cost and then sum is going to sum up the revenue it'll sum the cost and that'll give me the profit on a per cookie basis now here for if not found and all these others i'll leave those as is for now and i'll click on ok so here now i have my x lookup in place now i'm going to take the value or the profit per individual cookie and i want to multiply this by the total number of units sold and then i'll close my parentheses and hit enter and just like that using x lookup together with sum i'm able to get the order profit or the overall order profit so one of the really neat things with x lookup is i can return multiple values and on the topic of multiple values why don't we dig in and see exactly how this works over on the next sheet called x lookup return multiple values now previously to get the revenue per cookie and the cost per cookie we entered x lookup into one column and then we entered it into the next column but instead of doing that i can simply pull all of that information back in just one formula how do we do that well once again let's type in x lookup and then let's open the parentheses now just to make it easier i'll click into fx but you could also enter the formula right here this opens up the function arguments again and once again the lookup value i want to look for chocolate chip and over here i can select my lookup array and once again my lookup array is over here so i'll select this column right there and next i need to select my return array and just like before you can select multiple columns to return so here i'll select both revenue and also cost and then i'll pull it down and over here i'll leave the others as is and then let's click on ok now check that out so not only did it return the revenue but it also returned the cost per cookie so with just one formula it sent an array back and i was able to fill that out so that is pretty cool now if i want to pull this formula all the way down once again i need to make sure that these are absolute references so here i'll select f4 f4 and i'll just set it so this table is all just an absolute reference then i'll hit enter and here i can pull the formula all the way down and here you'll see then that it will automatically look up both the revenue and the cost for each individual cookie so this works exactly how i wanted to but this is yet another benefit of x lookup now you might be thinking x lookup is pretty powerful and this is a i should probably be using this over vlookup and h lookup but wait there is more let's click into the next sheet called xlookup wildcard match and let's see how this works within x lookup there's an option called match mode and here you can see all of the different options now in vlookup we were able to do an exact match and we were also able to do an exact match but if none was found to return a smaller item with x lookup we have even more control we could find an exact match but if it doesn't find any it can return the next larger item so if you remember the earlier example with the closest match over here maybe instead of falling back to the smallest value maybe i want to go up to the largest value i can configure that using vlookup also down below i can also set a wildcard match so maybe i want to know well which one is the first customer over here that starts with a w i can use x lookup to do that and let's test this out using option number two i'll click in this cell and let's once again click on the fx over here let's select x lookup for the lookup value i'm simply going to enter quotes and then i'll type in a w and then i'll type in an asterisk and then i'll type in another quote so i want to find a w and i don't care what comes after the w that's what the asterix does over here i need to select my lookup array and just like we did before i'll select the customer name right here so i want to look up the customer over here and if it finds a customer over here well i want it to return the full customer name so i'll simply select this as my return array now right down here i need to select the match mode and i want this to be a wild card match so i'll enter 2 and then click on ok and look at that the first customer with a w in its name is wholesome foods and i get that value back so once again this is yet another benefit of x lookup lastly i also want to show you the last big benefit of x lookup and that allows you to define how it should search let's go over and this is the very last sheet in the set so congratulations for making it to the very end once we go through this you'll have a very good grasp of how x lookup works on this sheet i want to know when lola's last order was so over here on the left hand side you can see lola is one of our customers and she had an order on january 16th and it looks like her last order was on july 13th now with vlookup it's going to look from top to bottom so if i were to run a vlookup it would return this value but the nice thing is with x lookup i have access to these different search modes so here just like with with vlookup i could just start at the top and then work my way down but now i can also start from the bottom and work my way up if i enter negative one in for the search mode i also have binary search as an option and i can enter two or negative two and it's the same concept i could start from the top and work my way down or start from the bottom and work my way up with a binary search though you have to make sure that you sort the data in either ascending or descending order so kind of like we did with vlookup earlier now let's try this and let's try to find what the last item was with this negative one i'll go up here and let's click into this cell it doesn't really matter which cell we enter this in and let's click on the fx next let's click on x lookup for this one we want to look up lola so i'll type in lola and i'll make sure that i enter it in quotes so there we're looking for lola and here i want to look it up in this customer column so here i'll select the entire column with the customers and i'll just highlight it all the way to the bottom and then for the return array well i want to get the order date back so i'll go ahead and let's highlight this column and if not found i'll leave that as is i don't care about match mode but down below i can now set the search mode once again for this i want to start from the bottom and i want to work my way up so for that just like we saw earlier if i go down just a little bit we see that to perform a reverse search starting at the last item i need to enter a negative one so let me type in a negative one here now i'll click on ok and here now we see that lola's last order was on 7 13. and here i can confirm that if i go down the list we see lola and her last order was on 7 13. and that now wraps up all of the functionality of x lookup and so hopefully now by going through these different examples you're starting to realize the power of using x lookup especially compared to vlookup and also h lookup in fact there's really not much of a reason to even still use vlookup or hlookup when you have x lookup it can do everything that both vlookup and hlookup can do but it has even more power and customizability alright well if you now know how to use vlookup hlookup and also x lookup please give this video a thumbs up to see more videos like this in the future make sure to hit that subscribe button also if you want to see me cover any other topics on this channel leave a note down below in the comments that's where i get a lot of my video ideas from all right well that's all i had for you today i hope you enjoyed and as always i hope to see you next time bye [Music] you

Channel: Kevin Stratvert

Views: 135,609

Rating: 4.9597316 out of 5

Keywords: kevin stratvert, vlookup, hlookup, xlookup, vlookup in excel, excel, microsoft, microsoft excel, vlookup and hlookup, v lookup, v look up, vlook up, excel vlookup, how to use, how to use vlookup in excel, h lookup, tutorial, excel vlookup tutorial, vlookup formula, formula, function, what is, beginner, beginners, how to do, stratvert, kevin, lookup function, multiple sheets, between two worksheets, h look up, x look up, x lookup, help, in excel, hlookup in excel, xlookup in excel

Id: DZEPA9UhLBw

Length: 32min 9sec (1929 seconds)

Published: Wed Mar 10 2021