Help with VLOOKUP

[John PC]

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.

I think in Column A where say 210 = TX, I should change
210XXX to equal that city. For example I'd put 210813 =
Dallas, TX. 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!

 

[Arvi Laanemets]

Hi


You didn't mention where do you keep the list the City codes with according
responses. Let they be in X:Y
=IF(VLOOKUP(VALUE(LEFT(D1,3)),A:B,2,FALSE)="TX",VLOOKUP(VALUE(MID(D1,4,3)),X
:Y,2,FALSE) & ", "
,"") & VLOOKUP(VALUE(LEFT(D1,3)),A:B,2,FALSE)


Arvi Laanemets