array/lookup help - multiple references over multiple colums

**cards52** · 05-21-2009, 08:21 PM

Excel 2007

I have a list of a few thousand cell phone numbers I want to sms, so need to find their carrier and corresponding text email address. I purchased a list of all the area codes and prefixes with the carrier that owns the number and was planning on a simple vlookup, but it is a little more complicated. Here are the problems
Column A has is the area codes, so many duplications.
Column B has the prefixes.
Colum C has the 7th number from the phone number only if necessary.

For example, area code 201 with prefix 202 is owned by USA Mobility. There is nothing listed in column C because they own all of prefix 202 and there is only one row for this area code/prefix combo. But area code 201 with prefix 204 is listed over 10 different rows becase each 7th number with that area code and prefix are owned by different companies.

So, I need a way to type in a phone number and have the email extension, if available, returned. I am sure that this sounds rather confusing, but with the attached sample hopefully someone can point me in the right direction.

Thank you.

**shg** · 05-21-2009, 08:23 PM

Welcome to the forum.

Please take a few minutes to read the forum rules, and then amend your thread title accordingly.

**teylyn** · 05-21-2009, 08:59 PM

Hi,

I guess you need to combine column A, B and C in a helper column

=A2&B2&C2

and then perform the lookup on that helper column. I inserted a new column after C and put the formula in there. The formula will return text, not numbers, so you must make sure that your lookup value is also text, like here:

=VLOOKUP(TEXT(A39,0),D2:F36,2,FALSE)

Just out of curiosity, since I'm not familiar with the US cell phones and number schemes. I see that Area 201 and prefix 204 can be with or without a 7th number. How does that work? Does the overall length of the cell phone number indicate if the 7th number is being used?

**cards52** · 05-21-2009, 09:34 PM

Thanks for the reply Teylyn. The presence of the the 7th number is what is creating the problem because it is not always necessary to determine the carrier. For example 970/581/3 is owned by TMobile, while 970/581/4 is owned by Cingular. So, when the 7th number is present, it needs to be taken into consideration. That is why I need something to check my number against columns A,B, and C or a combination of them somehow.

**teylyn** · 05-21-2009, 09:40 PM

So, when you look at a phone number, how do you know if a second number is in the phone number?????? I just assume that a phone number has more than 7 digits. How do you know that

201/204/77777777 is meant to be 201/204/7/7777777 ?????

**cards52** · 05-21-2009, 09:44 PM

US cell phone numbers are all 10 digits, but you only need the first 7 to identify the carrier. Sometimes you only need the first 6 if the carrier bought all number in that area code/prefix combo.

**teylyn** · 05-22-2009, 12:18 AM

Still confused. In your example, you had

201 204 for Eureka Telekom
201 204 0 for Level 3 Communications
201 204 1 for RNK, INC.
etc. through to
201 204 9 for Level 3 Communications

If a number is always 10 digits, does

2012041234 belong to Eureka or RNK? How would you figure that out?

**DonkeyOte** · 05-22-2009, 03:04 AM

I think some sample phone numbers & expected results would be useful...

Regardless, could you perhaps add the three values together such that:

G2: =(A2*10000)+(B2*10)+C2
copied down

You could then possibly conduct a MATCH based on the first 7 digits of the phone number (though this may need to be coerced pending it's structure/input), eg (for sake of demo):

Phone Number to Search:
H1: 201/204/6234

Carrier:
I1: =INDEX($D:$D,MATCH(0+LEFT(SUBSTITUTE($H1,"/",""),7),$G:$G))
would return: SPRINT SPECTRUM L.P.

Email:
J1: =INDEX($F:$F,MATCH(0+LEFT(SUBSTITUTE($H1,"/",""),7),$G:$G))
would return: messaging.sprintpcs.com

Changing the number in H1 such that it became: 201/204/3234 would generate different results (YMAX)

Is that along the right lines ?