Translating codes into meaningful terms in Excel

Two tips for translating codes into meaningful terms in Excel

Feb 24, 2000
Jeff Davis
© 2002 TechRepublic, Inc.

Are there cryptic codes lurking in your Excel reports that might confuse your readers? Do they call and ask "What's this abbreviation stand for?" or "Who's agent 25?" Here are two tips to help you and your users convert those codes to meaningful data and head off those support calls before they start coming in.

For integer codes and short lists, use the Choose function approach
Suppose you're working with sales data that originated in a mainframe or proprietary accounting application. The data includes numeric codes corresponding to each sales agent, as shown in Figure A.

Figure A

This report won't help much if you don't know who agents 1, 2, 3, and 4 are.

The problem is that all of the people who will read this report won't automatically know which sales agent is represented by which number. Since the codes associated with the sales agents are integers, you can use the Choose function to display and print the correct name. Here's how:

Insert a new column to the right of the Agent Code column.
Label the new column Agent Name.
In cell B3, enter the formula =CHOOSE(A3, "Bob", "Tom", "Carol", "Alice").
Copy that formula down column B to the last row of data.

When you finish, your sheet will look like the one shown in Figure B.

Figure B

Using the Choose function to convert the integers to names makes this report much easier to read.

Why choose Choose?

The Choose function is a great tool in this case because it provides an "instant lookup" for up to 29 items. You feed the function an integer N (as its first argument), and Choose returns the Nth item in the list (the function's remaining arguments). This approach works best when the codes you want to convert are integers (or can easily be converted to integers) and when you don't have very many codes to translate in the first place. (If you have too many codes to convert, just editing the formula can get a little messy.) The down side to using Choose comes up when you add a new code or change the name associated with a code. Although you only edit the first formula, you still have to remember to re-copy that edited version down the rest of the column.

For complex codes and long lists, use the Vlookup function
The Choose function makes it easy to "look up" an integer code, but what if your codes look like the ones shown in Figure C? In order to translate those codes into more user-friendly terms, you'll want to use the Vlookup (vertical lookup) function instead of Choose.

Figure C

We'll use the Vlookup function to convert the job codes into meaningful terms.

To use the Vlookup function to convert the job codes, follow these steps:

Insert a new column to the right of the Job Code column.
Label the new column Job Type.
In a separate area of the worksheet, create a table listing each job code and its corresponding job type.
Select the table of job codes and job types, open the Insert menu, choose Name, and assign the name "Jobcodes" to that range.
In cell B3, enter the formula =VLOOKUP(A3,Jobcodes,2).
Copy that formula down column B to the last row of data.
When you finish, your sheet will look like the one shown in Figure D.

Figure D

This Vlookup function looks up a value from column A and returns the corresponding job type from column B in the lookup table named Jobcodes (A13:B16).

Benefits of Vlookup
Using Vlookup requires a little more effort on the front end to set up the original table, but it provides a couple of important advantages over using Choose:

You're not limited to 29 items; your Vlookup table can contain as many rows of data as you need.
When you use Vlookup instead of Choose, you enter the formulas only once. Because you name the lookup table and you use that range name in the formula, you simply add new rows or edit the items in the lookup table, and your Vlookup functions will always point to the correct range.

Only the beginning

We've only scratched the surface of what you can accomplish with the Choose and Vlookup functions, but using those tools to translate codes into user-friendly terms is guaranteed to make your reports easier to read. To comment on these tips or to share your favorite applications for the Choose and Vlookup functions, please post a comment below or send me a note.

Created Date: 01/18/2002 Last Reviewed: 01/18/2002 Rev. Date: