Please read, can't figure how to tell in subject what the need is! :)

[43fan]

Hello!

I have an area code list, that has all area codes for each state listed,
like this:
203, 475, 860, 959 <--- In cell A1
Connecticut<--- In cell B1

of course, all states are listed in column B, and all codes for each
state are in column A, with commas separating
each code.

I have a db with contact names and their phone numbers, with area code
broken out. The eventual thing is,
I want to search through my area code list and put the state it
belongs to in the contacts db.

Using vlookup, I get a #ref when I try to do this. I'm guessing
because each cell with the area code in it doesn't
only have one value but could have multiple ones separated by commas?

If this is the case, how can I easily manipulate the data around to
put one area code in each row with the corresponding
State in the cell adjacent to it?

Thanks!!
Shawn

 

[Frank Kabel]

Hi
try the following array formula
=INDEX(B1:B100,MATCH(TRUE,ISNUMBER(SEARCH("203",A1:A100)),0))

Replace '203' with a cell reference of the wantes array code

 

[Max]

Perhaps this might work ..

Assuming the data below is in Sheet1,
cols A and B, from row1 down
[ Area codes in col A, State in col B ]

203, 475, 860, 959 <--- In cell A1
Connecticut<--- In cell B1

In Sheet2
-----------
Assuming the area codes of your db are in col C, C1 down

Put in D1:
=IF(ISNA(MATCH("*"&C1&"*",Sheet1!A:A,0)),"",TRIM(OFFSET(Sheet1!$A$1,MATCH("*
"&C1&"*",Sheet1!A:A,0)-1,1)))

Copy D1 down

Col D will return the "State" from Sheet1

Unmatched area codes in col C will return blanks ""

 

[43fan]

Frank,

Thanks, but I must be doing something wrong. I get a #N/A error.

Is there an easy way to split out all the area codes into a separate cell?
Then VLOOKUP should do the trick, as long as they're sorted properly. I can
sort them and save them that way, no problem. I'll need to "run" the
routine to put the States in at least once a month on new data, and right
now that new data is approx. 2000 records each month.

Thanks!!
Shawn

 

[Frank Kabel]

Hi
enter it as array formula (with CTRL+SHIFT+ENTER)

For splitting the array code you could use 'Data - Text to columns'.
But the formula entered as array formula should do

 

[43fan]

Frank,

Duh! That got it! I think I was just hitting CTRL-ENTER and not putting the
shift in. I don't do all that much work with Excel, and especially with
array formulas, and forgot the proper entry sequence.

Thanks!!
Shawn

 

[Don Guillett]

I would suggest going here>copy the left list>copy the right list below>use
text to columns 1 on fixed width, 2 on delimited on dash >use a lookup
formula. You could even use data>filter>autofilter to filter by state.

http://www.prodial.com/areacodes-Num.html