Solved 50 states and counties

January 30, 2013 at 08:13:09
Specs: Windows 7

I made a lookup table of the 50 states and beside each state, the salesman's last name. I put a simple VLOOKUP formula in a speadsheet of projects, so that if the project was in CA, the salesman was TOM, in that cell. It worked great. However, now we are splitting 7 US states in half and the state will be shared by 2 salesmen - divided by county. My projects list will have the State and the County. My lookup table will now have the salesman's name, the state, and the county. I need a lookup formula that looks at the state, then the county, and shows the salesman's name as the answer inside the cell. I will only list the counties out on the lookup table, if it is a state we are splitting. The other 43 states, will not have any counties listed. Thanks

See More: 50 states and counties

Report •


#1
January 30, 2013 at 08:38:44

I think I have a general idea of your layout, and I haven't tested this, but let me toss this out at you.

Take a look at this site, which explains how to create Dependent Data Validation Drop Down lists.

http://www.contextures.com/xlDataVa...

One Drop Down could be for your states. A second could be for the counties in that state. Once a state is chosen, the county Drop Down would be populated with the counties for that state, or "ALL" if the state isn't split.

Your VLOOKUP would now look up the state and the county instead of just the state. You would append the county (or "ALL") designation to the state name in your table and modify your VLOOKUP to use that entire string.

e.g. Your table might be look like this:

         D               E
1     Territory        Name
2  Alabama_County1      Joe
3  Alabama_County2      Bill
4  Alaska_All           Tom

The Drop Down A2 might contain your state names, the Drop Down in B2 might contain your county names.

Your VLOOKUP could combine both Drop Downs e.g.

=VLOOKUP($A$2 & "_" & $B$2, $D$2:$E$57, 2, 0)

With Alabama in A2 and County2 in B2, the VLOOKUP would look for Alabama_County2 and return Tom.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
January 30, 2013 at 10:21:28

Thanks. This is my first post ever. I realize I did not provide all the facts. I will be importing a report from a subscription website. The city, state, and county will be in separate cells. I will create a lookup table that has the County, State, and Salesman's name.

I want the Vlookup formula to look at the separate cells on each line for state and county, and find a match in my massive lookup table of all US counties, with the state in a separate column, and fill in the salesman's name, that is in the third column of my lookup table.

Column F - project state (F1 is the label and the list runs from F2-for hundreds of projects down the page)
Column G - project county
Column K - the cell where I want my formula to put the salesman's name into
Column N - list of all US counties (about 3,000 lines down)
Column O - list of states that correspond to the counties list in column N
Column P - the name of the salesman that has that county

In my initial post, I said I would only list the counties if we were splitting a state in our sales map, but I found an excel list of counties on the US Census bureau site and it made it easy. It did have the county with a comma, and the state, but since the project lists I import have the county in its own cell, I separated the county from the state, and have it in its own cell in my 3 column lookup table.

Please let me know if my formula can find a state match, and then a county match as a 2nd qualifier, to put the salesman's name into the cells in column K.

Thanks


Report •

#3
January 30, 2013 at 11:19:24
✔ Best Answer

It seems like the concept I suggested should still work.

VLOOKUP can only look at one column, so perhaps you should go back to the orginal format of "county, state".

Let's say you put the "county, state" in Column N and the names of the salescritters in Column O, to get something like this:


           F               G         ...      N                     O
1   Project State    Project County       County, State        SalesCritter
2      Alabama           County2         County1, Alabama          Joe
3                                        County2, Alabama          Bob  

Now "format" your VLOOKUP to combine your entries from Column G (project county) and Column F (project state), including the "comma space".

=VLOOKUP(G2 & ", " & F2, $N$2:$0$3000, 2, 0)

G2 & ", " & F2 will return the current version of "county, state" (County2, Alabama) and VLOOKUP will look up that string in Column N and return Bob.

The reason I suggested the Drop Downs in my first response was to eliminate the chance of a typographical error when the County and State names are entered in the cells. If either of those are spelt wrong, the VLOOKUP won't find them. Since it sounds like you are downloading the data, I guess that typos aren't an issue.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions


Ask Question