0

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))

Improve this question
1
  • when I want to check if a value is in another table i tend to do something like =IF(ISERROR(MATCH(A:A,B:B,0)),0,1) where 1 indicates a match and 0 indicates no match, I will sometimes create a unique ID of sorts by concatenating some fields, is this what you are trying to do? Commented Apr 3, 2012 at 15:30

3 Answers 3

Reset to default
2

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

This is nice. It works. Wondering, do you know how to have vlookup do multiple sheets? I have one for different states. So, would an IF statement work? If state A is false, check this tab, etc etc.
you should create a column for State and then add them all to the same sheet, vlookup requires them all to be on the same sheet (otherwise you'd have to write some vba or include all 50 state sheets in your formula with 50 iferrors)
Don't know if anyone will ever use this code since it's so specific but here is what I modified it. I didnt need the extra column for phone number.xls because I added the concatenate to the formula itself. Less is good for me! :) I'll rate you. Thanks.
=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))
To update, I just added all TNs in one page. Thus avoiding to have to use multiple sheets. It runs slow for a few seconds but it still works. It's a copy of the main with all combined, so it won't matter since no one will use it.
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"

Improve this answer

2 Comments

I never use Lookup, but have seen you use it cleverly in a couple of answers lately. Have you, or anybody, written anything about these, what I think are, unconventional uses of Lookup?
+1 Adding unnecessary columns should always be avoided if possible. Nicely done.
0

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.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.