Link to home
Start Free TrialLog in
Avatar of M S
M S

asked on

index match for area code table

I would like to write an excel formula (index match?) to get the state for each area code in a list.  In other words, I have a list of records and each one has a phone number.  I already stripped the extraneous stuff and pulled out just the area codes.  There is a column of three-digit numbers now, e.g.
408
248
212
etc.

Now I would like to look each one up in a table such as the one at https://en.wikipedia.org/wiki/List_of_North_American_Numbering_Plan_area_codes#United_States

Step one in using index match is to get the match function to work.  I tried
=match(d3,b2:b52,0)
and this didn't work because column B CONTAINS the value in d3 -- the formula I wrote would have worked if the table had been constructed differently.
Avatar of Ryan Chong
Ryan Chong
Flag of Singapore image

pls provide a sample and we will work on it
Avatar of M S
M S

ASKER

Ryan, were you able to pull up the wikipedia table of area codes?  That is what I'm working with.
M S

Are you working with a table that has multiple area codes in single cells in column B?

Or have you converted the table to have each area code in a single cell in column B and the state repeated in column A?
Please find the attached with two sheets. Sheet called "US_Area_Codes" contains all the area codes for USA from the web link you provided with the help of Power Query. And Sheet1 has two formulas to get the state name of the area code listed in column A.
If you have area codes in different column on Sheet1, tweak the formula accordingly but you can still use the table on "US_Area_Codes" as a lookup table.
AreaCodes.xlsx
This question needs an answer!
Become an EE member today
7 DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform.
View membership options
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.