How to use vlookup in excel video tutorial
![how to use vlookup in excel video tutorial how to use vlookup in excel video tutorial](http://i.ytimg.com/vi/sbx1i41SP00/maxresdefault.jpg)
So, I'll select this array from A1 through B13. And this is really just where am I trying to find my lookup value and the answer is in the new Season lookup table that I just created. And then comma over to the next component of the function which is table array. My lookup value is going to be MONTH in cell D2 because month is the common field or key that will tie my two data sources together. So in cell I2, I'll start writing a VLOOKUP function. So, I'm going to insert a new column here. The next step is to go back to our raw data and insert a new column where we're actually going to input our lookup function. So, as you can see, we have four buckets of three months each to define each Season. And last but not least, September through November, we're going to call Fall. March through May, we're going to call Spring. So, January and February are Winter as well as December. We're going to say December through February equals Winter.
![how to use vlookup in excel video tutorial how to use vlookup in excel video tutorial](https://i.ytimg.com/vi/h90tp9aTYEc/maxresdefault.jpg)
So, in this case, we're going to keep it simple. And now, I need to define which Season maps to each Month.
#HOW TO USE VLOOKUP IN EXCEL VIDEO TUTORIAL SERIES#
So, I've got a series from one to 12 for Month. And select Fill Series which will just add one each row. So, for Month, I can just type 1 here and then use the auto fill feature. And basically, we're just going to drop in two columns of data here. I'm going to name this Sheet, Season Lookup. Okay, so the first step that we need to take is to create a new tab where our lookup data is going to live. So, let's jump over to Excel and give this a shot. So, essentially what we're doing in column C here is we're looking up the Month of each given Date, finding it in our Lookup data and returning the appropriate Season. So, as you can see, Month is our common field or key that's going to allow us to pull Season into our Source data file. And we've got Lookup data on the right which includes data for Month and Season. We've got Source data on the left which includes a column Date and a column for Month. So, taking a look at a simple example here. So, both of sources need to contain one common field and that's what allows you to merge the two sources together. One thing to keep in mind when you're using a function like VLOOKUP is that it requires a unique, common field or a key that is shared between your source data and your lookup data. And basically, it just allows you to join data from separate sources using a common lookup value. VLOOKUP is one of the most popular lookup reference functions in Excel. Now I can enter any valid ID, and VLOOKUP retrieves the correct information from the table.- All right, next up, we're going to be creating a new field to categorize season and we're going to categorize season using a function called VLOOKUP. Now I'll copy the formula down and adjust as needed.įor last name, I need to change the column number to "3".įor department, the column number is "5".Īnd finally, for start date, the column number is "6". When I press Enter, VLOOKUP uses the ID to retrieve the name "Julie" from column 2 of the table. We'll cover non-exact matching with VLOOKUP in another video. Otherwise, VLOOKUP may find the wrong value. Unless you have a good reason to allow non-exact matches, you should always enter "0" or FALSE to require an exact match. If set to "0" or FALSE, VLOOKUP will require an exact match. If set to TRUE or "1" (which is the default value) VLOOKUP will allow a non-exact match. Range_lookup is a confusing name for an argument, but it simply controls matching. First name is in column 2, so that's the number we need. In this case, the lookup value is the named range "id." Note that this value must appear in the left-most column of the table, since VLOOKUP only looks to the right.įor column, we need to provide a number that corresponds to the column number of the value we want in the table. VLOOKUP takes four arguments: the lookup value itself, the table to use for the lookup, the column number to use when retrieving a value, and finally, something called range_lookup. Now let's use VLOOKUP to get the first name. We're going to look up employees by ID, so let's go ahead and enter a valid ID, so we can verify what VLOOKUP is finding as we enter the formulas. By using named ranges, our lookup formulas will be easier to understand and easier to copy. The first thing I'll do is create a named range for the data in the table, and a named range for the ID that we'll be using to look things up. Let's use VLOOKUP to build a simple form that retrieves the information for a given employee based on their ID number. Here we have a list of employees in a table. The V stands for "vertical" which means you can use VLOOKUP to look up values in a table that's arranged vertically. VLOOKUP is one of the most important lookup functions in Excel.