Texas cities

  • Thread starter Thread starter john c
  • Start date Start date
J

john c

In Column A I have every area code in the US. 201, 202,
203....all the way to the last one.

In Column B I have the US State that matches that area
code. NJ, DC, CT.....etc.

I have it set up so when you put a 9 digit phone number in
Column D, it uses VLOOKUP to put that corresponding US
State in Column E. That formula is =VLOOKUP(VALUE(LEFT
(D1,3)),A:B,2,FALSE).

Seem simple? OK....

I need to reconfigure this formula to recognize the first
3 digits, and when that equals 'TX' for Texas, I need it
to read the next 3 digits so that it can spit out the City
AND State. This is the problem.....I am not sure where I
should insert the cities into my raw data. For example,
in Column A where I have area code 210 should I add 210566
for Dallas, and then keep adding all of the cities within
area code 210 underneath? Do I need to make a separate
list of Texas cities?

I think it may be incorporating an IF Statement of some
kind onto my original VLOOKUP formula.

If I'm not totally clear please tell me. You guys have
been a great help, I just need this last bit!
 
I'm finding it easiest to add a separate list of the cities. for example,
have a couple of rows empty after your current state data then start with
566 in column A and Dallas in Column B. This is where it gets interesting.
I found a somewhat easy way to use what you have here in 2 columns, but I
have yet to find a way to fit the function to make it into 1 column.
Anyway, I added a column F next to your state VLOOKUP. the contents were as
follows: (My test data was in Columns A and B starting at row 20)...

=IF(E4="TX",VLOOKUP(VALUE(MID(D4,4,3)),A$20:B$26,2,0),"")

Basically, if "TX" is the state, then do a secondary VLOOKUP on the exchange
and place the value in column F. If you need it all in a single Column, I'm
thinking you could somehow put the 2 functions together, but it would be
quite large. Let me know if this works for ya!
 
Back
Top