Picking Region Data from State Information
 

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?

Picking Region Data from State Information
 

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.


--
Gary''s Student - gsnu200774


"Robert F. Dally" wrote:

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?