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)

snflupigus < wrote:
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 using
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 reference
with vlookup and simply build a function with an array that contains
all area codes and the states they're in.

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

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

Thanks


---
Message posted from http://www.ExcelForum.com/