You can watch me perform these steps on video.
V-what? It may have an odd name, but the VLOOKUP function in Excel can be a real lifesaver. In brief, it grabs values from one location, matches them up with the corresponding values in a different location, and fills in a column with the information from the first location. Still confused? Let’s look at an example.
An aside: There’s a newer version of VLOOKUP, called XLOOKUP, in the latest versions of Excel. But since they’re pretty similar and you might not have that version of Excel, we’ll stick with VLOOKUP for now.
Why you might find it useful
Here’s my spreadsheet listing the locations and dates of traffic accidents in Boston. I’d really like to show in which season the accidents occurred. But that information isn’t in my spreadsheet; I only have months and days. I do, however, have a separate list of months (identified by number) and their corresponding seasons, like this:
1: Winter
2: Winter
3: Spring
[etc.]
So how do I get a new Seasons column that uses my list of months and seasons to tell me in which season each accident occurred? Is my only option to painstakingly enter all the seasons by hand? Of course not! This is where the VLOOKUP comes in. You’ll instruct Excel to refer to your list of seasons to automatically fill out a new seasons column according to the corresponding month.

Some terminology
To help specify which data we’re talking about, let’s call our big dataset—the list of car accidents—raw data. And we’ll call our list of seasons and corresponding months our lookup table. In order for VLOOKUP to be useful, your two tables must have one column with the same kind of data in it. In our case, that’s the month column. We’ll call that the key, since it “unlocks” the relationship between the two tables.

Insert a new column
Let’s create a new column in our raw data table, to hold the season information we’re going to import. Click on a cell in column E. Then, either right-click your mouse, or press control and click with your keyboard, to view a pop-up menu. From that pop-up menu, select Insert… Finally, from the Insert pop-up window, select Entire column and press OK. This adds a blank column to your existing data. (Actually, this new, blank column could go anywhere in your table, but it’s nice if it’s close to the MONTH column.)

Label your new column
We always put our column names at the top! So in the top-most cell of the column you just created, enter the name for that column: SEASON.

The VLOOKUP formula
We’re about to type a formula into the first cell in column E. But before we do, let’s look at what information Excel is expecting. When you specify the VLOOKUP formula, Excel is expecting you to tell it:
- Which cell contains the first value you want Excel to look up?
- Which table is the lookup table?
- In the lookup table, which column contains the information you want in your new column? (Use a number to specify the column.)
- Is an approximate match OK, or do you need an exact match?
Notice that the formula expects you to enter each piece of information followed by a comma, with a parentheses around the entire set of variables. If this seems overwhelming, don’t worry. It makes more sense when you actually do it.

Enter the VLOOKUP formula
Click on the first empty cell in your new SEASONS column. In Excel, you indicate that you’re typing a formula by beginning with an equals sign (=). So type that, followed by VLOOKUP to specify the formula you’re using.
- Then open the parentheses and enter the cell coordinates for the first value you want Excel to look up. In my case, that’s the first cell in the MONTH column. I can indicate that by either typing C2 or just clicking on that cell with my mouse. Then type a comma.
- Next, we need to tell Excel where to go to get the information you want it to fill in. Do this by clicking on the Season Lookup tab to open the Season Lookup worksheet, and then using your mouse to select the entire lookup table. Then type a comma.
- Next, we need to tell Excel which column in the lookup table contains the information we want to fill our new SEASONS column with. Type 2 to indicate that the appropriate column is the second one over from the left. Then type a comma.
- Finally, tell Excel whether an approximate match is OK. Type or select FALSE (or 0) to indicate that you want an exact match.
- Then close the parentheses.
To summarize, your formula should look like this:
=VLOOKUP(C2,'Season Lookup'!A1:B13,2,FALSE)
If you did it right, and you press return, you should find that the first cell of your SEASON column contains the word Winter!

A tricky but critical extra step
You did the hard part! Now we want to copy the formula to the entire column. But there’s one extra step we need to take!
When you paste a formula into a new cell, Excel, by default, adjusts the locations of the cells you specify to reflect the new location. For example, C2 in the above formula becomes C3, to indicate that you’re referring to the value in the next row down.
Usually, that’s helpful. But while we want Excel to adjust the value in the lookup_array, we don’twant it to adjust the table_array (the table that contains our seasons list). We want that to remain the same in every row of the column.
To tell Excel not to mess with the table array, we need to turn our currently relative cell reference into an absolute cell reference. To do this, we’ll add a dollar sign ($) in front of every character in the cell reference in the table_array portion of your formula. It should look like this:
=VLOOKUP(C2,'Season Lookup'!$A$1:$B$13,2,0)
Now, if you click on cell E2 and then use the green handles to fill in a few rows below, you’ll see that Excel uses the correct range for its table array.
Fill the entire column with your formula
Now the final step: Fill in the entire column with your formula. You could do this by dragging the green box all the way down, but that would take forever. Let’s do it the easier way.
Click on the last cell you “filled in” in the SEASON column. (For me, that’s E5, because I dragged the green box down a few rows.) That will reveal that green box again. Now, carefully move your cursor so that it hovers over the handle at the lower right-hand corner of the green box. You can tell you’re in the right place because the white plus sign that indicates your cursor will turn black.
Now, double-click. And voila, you have a column full of seasons!
