Technology

Excel – Converting State Codes to States

Automatically switch state codes to names by taming unwieldy “IF” statements in Excel array formulas.
Bill JelenAugust 31, 2011

Editors Note: To share a tip with other readers, or suggest a topic for Bill Jelen to cover in an upcoming column, send an e-mail note to Bill at [email protected]

Reader Raveed K. wins a copy of Learn Excel 2007-2010 from CFO and MrExcel.com for his question: “I need to convert state codes to state names. This is easy when there is a table with which you can use VLOOKUP, but without such a table, the IF statements become too unwieldy. Access and SQL offer the SWITCH function, does Excel have anything similar?”

Excel does offer the CHOOSE function, but it only works if the variable is numeric from 1 to 254. So this will not work for state codes.

Drive Business Strategy and Growth

Drive Business Strategy and Growth

Learn how NetSuite Financial Management allows you to quickly and easily model what-if scenarios and generate reports.

Rather than trying to nest too many IF statements, you can simulate the SWITCH function by embedding the array of options and their answers inside of an array and then embedding that inside of a VLOOKUP function.

Build the array using syntax such as this:

     {Value 1,Answer 1; Value 2, Answer 2; Value 3, Answer 3; … Value N, Answer N}

The important part to notice in the syntax is that the value and the description are separated by a comma. Use a semicolon to move on to the next state.

There are times when you might have two lookup results for each state code, perhaps a state name and a region code. Again, use commas between values and a semicolon to move to the next state code, as I show below.

     {“AL”, “Alabama”, “Southeast”; “AK”, “Alaska”; “Northwest”; … }

Once you’ve typed the list of codes inside the curly braces, include that whole text as the second argument in the VLOOKUP function.

Of course, the difficult part is typing the complete list of values. If you truly happen to need state codes, access this workbook and copy the formula from cell B2 to your workbook. The workbook link also contains the reverse formula, to convert a state name to a state code (copy from cell C2).

Bill Jelen is the author of 33 books about Microsoft Excel and the host of MrExcel.com. Submit questions to be considered for future articles simply by sending an e-mail note to [email protected] If your question is selected as the subject of a column, you’ll win one of Bill’s Excel books.