Editor’s 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.

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.