Function to cnvrt Phone to State w/out cell ref

[snflupigus]

I am trying to write a function to convert a phone number to state..

Currenly I have using vlookup and it works fine. here is what Im usin
now...

=VLOOKUP(LEFT(A2,3)*1,State,2,FALSE)

State is a named range in a seperate sheet within the workbook.
A:A are phone numbers/account numbers like 2015552222

I would like to get rid of the sheet containing the cells i referenc
with vlookup and simply build a function with an array that contain
all area codes and the states they're in.

I also want to write another function that then converts states int
sales regions. West, Midwest, East, etc....

I have failed horribly at trying to do this... please help. What I
doing works now, but i want the functions so it is easier for other
not so good with excel to process the data we already have on thei
own.

Thank

 

[snflupigus]

btt

 

[JWolf]

No need to use VBA.
In cell B2, go Insert, Name, Define and in the refers to box type in:
=VLOOKUP(LEFT(A2,3)*1,State,2,FALSE)
Then in names in workbook type in a function name of your choice e.g.
actostate
Click OK
In cell B2, enter the formula =actostate
In cell C2, enter the formula =actostate, etc.
(Assumes that values in column to left of formula contain area code)