{"id":334,"date":"2022-01-28T04:11:33","date_gmt":"2022-01-28T04:11:33","guid":{"rendered":"http:\/\/miriamposner.com\/classes\/is270s23\/?page_id=334"},"modified":"2022-01-28T04:45:20","modified_gmt":"2022-01-28T04:45:20","slug":"use-the-vlookup-function-in-excel","status":"publish","type":"page","link":"http:\/\/miriamposner.com\/classes\/is270s23\/organizing-data\/use-the-vlookup-function-in-excel\/","title":{"rendered":"Use the VLOOKUP function in Excel<\/a>"},"content":{"rendered":"\n

You can watch me perform these steps on video<\/a>.<\/em><\/p>\n\n\n\n

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.<\/p>\n\n\n\n

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.<\/a><\/p>\n\n\n\n

Why you might find it useful<\/h2>\n\n\n\n

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:<\/p>\n\n\n\n

1: Winter<\/p>\n\n\n\n

2: Winter<\/p>\n\n\n\n

3: Spring<\/p>\n\n\n\n

[etc.]<\/p>\n\n\n\n

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.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

Some terminology<\/h2>\n\n\n\n

To help specify which data we’re talking about, let’s call our big dataset\u2014the list of car accidents\u2014raw data<\/strong>. And we’ll call our list of seasons and corresponding months our lookup table<\/strong>. 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<\/strong>, since it “unlocks” the relationship between the two tables.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

Insert a new column<\/h2>\n\n\n\n

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…<\/strong> Finally, from the Insert<\/strong> pop-up window, select Entire column<\/strong> and press OK<\/strong>. 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<\/strong> column.)<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

Label your new column<\/h2>\n\n\n\n

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.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

The VLOOKUP formula<\/h2>\n\n\n\n

We’re about to type a formula into the first cell in column E<\/strong>. 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:<\/p>\n\n\n\n

  1. Which cell contains the first value you want Excel to look up?<\/li>
  2. Which table is the lookup table?<\/li>
  3. In the lookup table, which column contains the information you want in your new column? (Use a number to specify the column.)<\/li>
  4. Is an approximate match OK, or do you need an exact match?<\/li><\/ol>\n\n\n\n

    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.<\/p>\n\n\n\n

    \"\"<\/figure>\n\n\n\n

    Enter the VLOOKUP formula<\/h2>\n\n\n\n

    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<\/strong> to specify the formula you’re using.<\/p>\n\n\n\n