Join Chris Dutton for an in-depth discussion in this video VLOOKUP, part of Building a Dynamic Heat Map in Excel.
- 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. VLOOKUP is one of the most popular lookup reference functions in Excel. And basically, it just allows you to join data from separate sources using a common lookup value. 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. So, both of sources need to contain one common field and that's what allows you to merge the two sources together.
So, taking a look at a simple example here. We've got Source data on the left which includes a column Date and a column for Month. And we've got Lookup data on the right which includes data for Month and 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. 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, let's jump over to Excel and give this a shot. Okay, so the first step that we need to take is to create a new tab where our lookup data is going to live. I'm going to name this Sheet, Season Lookup. And basically, we're just going to drop in two columns of data here. The first is going to be the Month. The second is going to be the Season. So, for Month, I can just type 1 here and then use the auto fill feature.
And select Fill Series which will just add one each row. So, I've got a series from one to 12 for Month. And now, I need to define which Season maps to each Month. So, in this case, we're going to keep it simple. We're going to say December through February equals Winter. So, January and February are Winter as well as December. March through May, we're going to call Spring.
June through August is Summer. And last but not least, September through November, we're going to call Fall. So, as you can see, we have four buckets of three months each to define each Season. So, this is our lookup table. 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, I'm going to insert a new column here.
I'm going to call it SEASON. So in cell I2, I'll start writing a VLOOKUP function. 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. And then comma over to the next component of the function which is table array. 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.
So, I'll select this array from A1 through B13. That's my table array and the last component of the formula is the column index number. So, where are the values that I'm trying to return here. In this case, they're in the second column of my lookup table. So, that's where Season lives in column B. So, I'm just going to type a 2. And then last but not least, you can choose Approximate or Exact match. I'm going to choose a 0 or Exact match.
It's the same thing as typing false. And that means that I'm looking for the exact Month number. I don't want any approximations or anything like that. So, I'll close off that function. Hit enter. And there you go, I've got Winter. Now, the one thing that I need to adjust here is my cell references. So, at this point, my table array is what's called a relative reference meaning that if I move this formula, this reference will shift. So, I'll show you by example.
If I drag this down, you'll that reference table is moving down as well. So, what I need to do is make sure that lookup table stays fixed. I can do that by fixing the reference types. And the shortcut to do that is to put your cursor inside of the cell reference and hit F4 and that will insert fixed references. So, I always want to fix the reference to be 13. And now, if I drag this formula down, you'll see that the reference always remains A1 through B13 because my lookup table is never changing.
So, there you go, I can apply it down with auto fill and I can see that all four of seasons are populating. And there you go, we have a simple SEASON variable. So, to recap what we just did. We used the VLOOKUP function to define a simple version of Season by creating a lookup array based on the month in which each accident occurred.
Chris Dutton provides hands-on examples designed to showcase why certain functions—DATEVALUE, VLOOKUP, COUNTIF, and more—are so valuable to mapping data. He also shows how to use conditional color-scale formatting to map your data matrix, and find different ways to examine the same data with different criteria, using dynamic filters. The course wraps up with a few finishing touches to make your heat map even more useful and engaging, including a preview of what's possible with the Power Map addin.
- Setting up the heat map project
- Using functions such as WEEKDAY and VLOOKUP
- Adding conditional statements
- Formatting with the color-scale feature
- Aggregating data with SUM
- Adding dynamic filters
- Creating formula-based formatting rules
- Adding additional analytics