Looking up two entries in a listing.

[Alex]

Note: I don't think that a lookup function can fix this
problem because there are multiple counties in different
states with the same name. The solution cannot solely
rely on the county name because several states have the
same county names in them.

I am working a converting old county migration data into
a usuable form. I have one worksheet with counties and
their respective states, and another worksheet with a
listing of state and county FIPS information.
For example:
C8081coi!
A B C D
1
2 To To From County Name From
3 St Co
St
4 Abbr
5
6
7 ADAMS CO
8 DENVER CO
Sheet0!
A B C D
1 St Co County Name St Abbr
2 08 001 Adams CO
3 08 003 Alamosa CO
4 ...
5
6
7
8
I need to find a way to lookup a county name with its
respective state in my master list of all counties.

 

[Bill Renaud]

This example is more advanced than your original post. Chip Pearson's web
page on "Looking Up Data In Tables" at
http://www.cpearson.com/excel/lookups.htm would have to be modified to fit
your problem. Basically, I would try the following:

1. Add a new column (I labeled it "State, County" in column $E) on the
lookup worksheet that is the master list of counties and states (I called it
"Master List" in my testing).
2. Assuming that County is in column $C, and State is in column $D on the
"Master List" worksheet, enter the formula "=D2&", "&C2" in column $E for
the first county/state pair of data on row 2. This effectively concantenates
the State and County information together into a single cell. The result in
the first row of data in your example would then be "CO, Adams" (note the
space after the comma).
3. Now, on your worksheet where you want to look up the county and state
numbers, use a MATCH function nested inside of an INDEX function to return
the County number from the Master List worksheet. So, on row 7 of your data
worksheet, where you have "ADAMS" in column $C and "CO" in column $D, put
the following formula in the column where you want the County number from
the Master List worksheet:
=INDEX('Master List'!$B$2:$B$13,MATCH(F7 & ", " & E7,'Master
List'!$E$2:$E$13,0))

(My list of states and counties on the Master List was from row 2 to row 13,
for testing purposes.)
In the MATCH function, the first argument (F7 & ", " & E7) concantenates the
State and County together, then looks it up in column $E and returns the
relative position to the INDEX function which locates the value in column $B
of the Master List.

The State can be looked up in similar fashion, although you could probably
just use the State column on the Master List worksheet, since the State code
number should be the same for all rows that are the same state.