Picking Region Data from State Information

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Robert F. Dally - 19 Mar 2008 16:21 GMT

Using Excel 2003 on a PC, I want to do the following:

I work for a nationwide company that breaks the states into 11 regions. I
want my spreadsheet to automatically calculate which region a state is in
(meaning, for example, I put in "Joe Schmo" lives in "UT" and the worksheet
inputs "7" in the REGION column). What would be the most parsimonious means
of doing this?

Reply to this Message

Gary''s Student - 19 Mar 2008 17:11 GMT

Create a lookup table. FOr example in L1 thru M56:

Alabama    7
Alaska    5
American Samoa    5
Arizona    2
Arkansas    4
California    4
Colorado    4
Connecticut    10
Delaware    6
District of Columbia    8
Florida    6
Georgia    1
Guam    3
Hawaii    7
Idaho    9
Illinois    6
Indiana    5
Iowa    1
Kansas    10
Kentucky    7
Louisiana    4
Maine    5
Maryland    2
Massachusetts    1
Michigan    9
Minnesota    10
Mississippi    7
Missouri    6
Montana    2
Nebraska    5
Nevada    9
New Hampshire    2
New Jersey    2
New Mexico    5
New York    9
North Carolina    9
North Dakota    2
Northern Marianas Islands    6
Ohio    5
Oklahoma    5
Oregon    4
Pennsylvania    10
Puerto Rico    2
Rhode Island    3
South Carolina    10
South Dakota    1
Tennessee    10
Texas    8
Utah    4
Vermont    2
Virginia    5
Virgin Islands    11
Washington    9
West Virginia    7
Wisconsin    6
Wyoming    7

IF a state or territory is entered in D1, then:
=VLOOKUP(D1,L1:M56,2) will return the correct region.

Signature

Gary''s Student - gsnu200774

> Using Excel 2003 on a PC, I want to do the following:
>
[quoted text clipped - 3 lines]
> inputs "7" in the REGION column). What would be the most parsimonious means
> of doing this?

Reply to this Message