Excel if statement with multiple if statements

Question

Help!

I enjoy Excel but I wanted to see if this were an option. I might even consider coding this in a simple javascript or so file.

I am trying to make a code that can be copied and dragged down on each new file that is made from our server.

On the file, there are NPA's and NXX's. If you aren't sure what that is, it is the area code and prefix. Like 555-111-2222. 555 is the NPA and the 111 is the NXX.

Well, I would like to create an IF statement that capture the various NPA and NXXs combinations and what their carrier is for our markets.

For example, 479-203 is Centurylink. However, 479 is in column N and 203 is in column O.

While I can create, if N2 = "479" and O2 = "203", CenturyLink, --- do another if statement.

I was not sure since I have another excel document that does not change with the values present if I could use that to help answer the question.

In columns A and B on another document have the NPA and NXXs. In the same file, column L has the carrier name.

Is there a search capability that I can use for this? If you name the function, I'll look it up too. I'm not picky. :)

Edit:

=IF(OR(N10="479",N10="870"),VLOOKUP(CONCATENATE(N10,"-",O10),[TnList.xls]AR!$A:$D,4,FALSE),VLOOKUP(CONCATENATE(N10,"-",O10),[TnList.xls]KS!$A:$D,4,FALSE))

Answer 1

You will need to add another column to the left of the data in your carrier spreadsheet and fill it with =CONCATENATE(B2,"-",C2) first:

CarrierSpreadsheet.xls:

NPA-NXX   | NPA     | NXX     | Carrier
429-203   | 429     | 203     | CentryLink
123-111   | 123     | 111     | CarrierA
456-222   | 456     | 222     | CarrierB
789-333   | 789     | 333     | CarrierC

Then add 2 columns to your phone number data determine the carrier from your phone number spreadsheet. Column B needs to be filled with =LEFT(C2,7) and column A needs to be filled with =VLOOKUP(B2,[CarrierSpreadsheet.xls]Sheet1!$A:$D,4,FALSE):

PhoneNumbers.xls

Carrier    | NPA-NXX | Number
CentryLink | 429-203 | 429-203-9999
CentryLink | 429-203 | 429-203-8888
CarrierB   | 456-222 | 456-222-9999
CarrierC   | 789-333 | 789-333-9999

Let me know if you have any questions.

PS - If this answer works for you please click the grey checkmark to the upper left corner or my answer.

Answer 2

You can use a LOOKUP formula which avoids adding extra columns, e.g.

=LOOKUP(2,1/(Data!A$2:A$100=N2)/(Data!B$2:B$100=O2),Data!L$2:L$100)

That assumes Data worksheet is your "other file"

Answer 3

You could name the columns; then the formula can simply refer to the names. But you'd have to name the relevant columns in each document; I'm not sure how well that would work for you.