Picking Region Data from State Information

[Robert F. Dally]

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?

 

[Gary''s Student]

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.