Need to take my area code list, and make a state list.

**Tommy1005** · 07-28-2009, 04:44 PM

Hey, I'm new here, so pardon me if I posted in the wrong place.

Basically I have a giant list (50,000+) of area codes (like 312, 216, 517, etc...) and I would like to make a new column right next to that, which takes those area codes and transcribes them into states so it would be (IL, OH, MI, etc...)

I have a table on the 2nd page of my excel workbook which has every single area code listed in column A, and in column B it has all the state symbols. I got it from here: http://www.lincmad.com/locator.html

How would I go about making this new column on page 1 of my workbook which takes all the area codes I have and makes a brand new list of the states by looking up the area code/state table I have on page 2 of the workbook?

Thanks so much! I've been stumped for a while now

**martindwilson** · 07-28-2009, 04:56 PM

with your codes in col a sheet 1 starting in A2 and your table codes/states in sheet 2 codes in A states in B
(assuming a1/b1 contain headers such as area code/ state on both sheets)
in sheet 1 cell B2
put
=VLOOKUP(A2,SHEET2!$A$2:$B$1000,2,FALSE)
(Where $B$1000= last cell in your range on sheet 2 ,change it if it isnt)
drag down formula to end of list on sheeet 1

**Tommy1005** · 07-28-2009, 05:01 PM

Heres some pictures so you can see what I am trying to do, I'm sure its really simple but i cant figure it out..

This is the area codes I have that I need to turn into states to the column directly to the right of it: http://img21.imageshack.us/i/areacodelist.png/

And this is the list I have that has all the area codes in the US, and the states that belong to them: http://img21.imageshack.us/i/lookupn.png/

Obviously both lists are a lot longer, but I can't take a screen shot of 300 lines in excel =)

**Tommy1005** · 07-28-2009, 05:23 PM

Martin I tried what you said but its not working: heres the screen shots of whats going wrong, its giving me Value# error.

I set it up exactly how you said too

http://img6.imageshack.us/i/sheet1.png/
http://img18.imageshack.us/i/sheet2.png/

**oldchippy** · 07-28-2009, 05:31 PM

Hi,

You need to check that the "numbers" on Sheet1 and Sheet2 (Column A) are in the same format.

Are they both in TEXT format or GENERAL or NUMBER, they need to be the same on both sheets.

**martindwilson** · 07-28-2009, 05:40 PM

what i said still goes!

**Tommy1005** · 07-28-2009, 06:06 PM

Hey Martin, that file codes is amazing, but there is something strange going on..

When I replace all the area codes you had in there, with the list of my area codes, everything goes # N/A for some reason...

But if i manually type in an area code... it works.. And I am using the same font and size when im copying pasting.

Martin i uploaded my excel file im trying to do it to here: http://drop.io/hcfxmbw

You could probably do it in a few seconds.

**Tommy1005** · 07-28-2009, 06:19 PM

GOT IT!

Used this: =VLOOKUP(A2,Sheet2!$A$1:$C$388,2,FALSE)

and i had to convert all my numbers from Text to Numbers...

**Tommy1005** · 07-28-2009, 06:20 PM

thanks soo much!!

**oldchippy** · 07-29-2009, 03:55 AM

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save