A recent case involved some voter registration data that needed to be analyzed. On one Excel spreadsheet, the voter’s party was listed as an alphanumeric value called "Pcode" and not the political party. This coding wasn't intuitive. For example, “D” was for “American Independent Party”, but some thought it meant “Democratic Party”.
One way to solve this problem is to create a worksheet with the Pcode and translation and
then have Excel use the VLOOKUP function for the party name. You might think of VLOOKUP as an Excel translator.
Add a column called “Political Party” to the original worksheet to show the information from a lookup table.
Creating a Lookup Table
A lookup table includes the values you wish to "lookup" such as our Pcode and the translation such as political party. You can place this table on the same worksheet, but for this Excel tutorial we'll add a worksheet called "Political Party".
How to Create a Lookup Table,
1. First, create the worksheet (labeled Voters) shown above using the same column headings and data.
2. Click the starred folder on the right of the existing tabs.
3. Rename this new worksheet tab with a descriptive name such as “Party Codes”
4. In worksheet Party Codes, enter in column A the unique values that exist on your Voters worksheet. In this example, these were the codes that showed in the Pcode column in the thumbnail. These values should be in ascending order.
5. In Column B, enter the translated value (or the value you want to transfer in the VLOOKUP function). You can have more values in column A than appear on your main spreadsheet. For example, you can have an entry for “Citizen Party” even though there was not a registered voter with that affiliation.
Using the VLOOKUP Function
Excel’s VLOOKUP function uses 4 pieces of information. The function panel may seem intimidating with the terms, but it’s simpler than it looks.
To lookup a value using VLOOKUP,
1. Add your new column on your original worksheet that will display the info pulled from the Lookup table. In this example, add a column called Political Party in Column D. This is where you will insert the Excel function.
2. Place your cursor in the first blank cell in that column. In this example, this is cell D2.
3. Select fx (above Political Party) and the Insert Function dialog will appear.
4. In the Search for a function: text box, type “vlookup” and click Go.
5. Highlight VLOOKUP and click OK.
Defining the VLOOKUP Values
After you click OK, Excel’s Function Arguments dialog appears and allows you to define the four values. [Note: EXCEL 2010 will look a little different that this figure, but the functional arguments are the same as you will need.] You’ll see that your starting cell and the formula bar show the beginning part of the function =VLOOKUP(). The Function Arguments dialog adds the needed data elements that will display between ().
For illustration purposes, the Party Codes are overlaid on the worksheet to show the relationships.
1. Lookup_value – Think of this field as your starting point. In this example, click cell C2 so the value is filled in the dialog. This request Excel take the value of C2, which displays as the Pcode of “A”, and find the matching political party on the lookup table on the Party Codes worksheet.
2. Table_array – This is the range for your lookup table. The range can be on your existing worksheet or another worksheet such as our “Party Codes”. When you click another tab and define the range, Excel prepends that tab name to the range such as ‘Party Codes’.
Rules & Caveats
There are several rules to remember about this table array. Rule 1 - The left column must contain the values being referenced. In other words, you can't have the first column be Political Party. Rule 2 - You can’t have duplicate values in the leftmost column of the lookup range (much like a primary key). You can't have two entries with the value “A” with one being “Democratic” party and another “A” for the “Humanist” party. Excel would complain and return some form of error message. Rule 3 - When referencing a lookup table, you don’t want your cell references to change when you drag and fill to populate the other cells with the VLOOKUP function. As example, if you want to use the same function in cells D3 through D7, you don’t want my lookup cell references to shift each time you move down to the next cell. You need the cell references to be the same. After you define your range, you need to press F4 which will cycle through absolute and relative references. You want to select the option that includes a $ before your Column and Row. ( 'Party Codes'!$A$2:$B$45. ) You can get around this if you know how to use Excel name ranges. Understanding how to use relative and absolute referencing in EXCEL is critical to using it effectively. |
Col_index_num – This is the number of the column on your lookup table that has the information you need. In this example, we want column 2 from the Party Codes worksheet which has the name of the political party.
Range-lookup – this field defines how close a match should exist between your Lookup_value (C2) and the value in the leftmost column on our lookup table. In our case, you want an exact match so use “FALSE”.
After clicking various cells, my dialog looks like this:
You can see in the formula bar above, you now have more information based on the entries in the Function Arguments dialog box.
The other item of interest is that when you build these functions, Excel displays the result in the Formula result = text line. This is great feedback which can show if your function is on target. In this example, you can see Excel looked up the Pcode of “A” and returned the Political Party “Democratic”.
Copying the VLOOKUP Function to Other Cells
It doesn’t make sense to use VLOOKUP for one cell in your Excel spreadsheet. Instead, you will want to copy the function to other cells in the same column.
To copy VLOOKUP to other column cells,
1. Click the cell containing the VLOOKUP arguments. In our example, this would be D2.
2. Grab the cell handle that displays in the lower right corner.
3. Left-click and drag down the cell handle to cover your column range.
Note two things:
First: If you don't change to absolute reference as mentioned in Rule 3, you will see the table array that is used shift by one cell as we dragged down through the other cells. This will produce erroneous results.
Second: Your initial results should indicate N/A for Robbins Terry and Talbot Angie. This is because their party affiliation is not listed on the VLOOKUP table. Change Robbins Terry to a code of C and Talbot Angie to a code of E. See how the correct response is now included. This is a powerful feature of VLOOKUP. In cases where invalid codes are used, N/A will be returned and this will inform the user that a problem is most likely present.
SAVE THE FILE after making the corrections for Robbins Terry and Talbot Angie.
Don't forget to include a description worksheet describing what you plan to accomplish with this Workbook.
VLOOKUP is a powerful Excel function that can leverage spreadsheet data from other sources. There are many ways you can benefit from this function. In this example, you used a 1:1 code translation, but you could also use it for group assignments. For example, you could assign state codes to a region such as CT, VT, and MA to a region called “New England”. And for the adventurous, you can use VLOOKUP in your formulas.
Save file as Lastname_Firstname_Excel_3 and e-mail before class begins on the due date. Subject line: Lastname_Firstname_Homework, for example Doe_John_Excel_Project_3.