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:
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 recopy 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 userfriendly 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:
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:
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 userfriendly 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. 
Copyright © 19992001 TechRepublic, Inc.
Created Date: 01/18/2002 Last Reviewed: 01/18/2002 Rev. Date: