1. ## Formula Help

I have a question regarding an excel formula.

I have a worksheet that has a range of phone numbers in a column. This column is followed by another column that places each of these numbers into 'wireless group' (an assigned number). I have another column that has phone number and I need an equation that searches this column and as it finds a match of the phone number to the column that has the 'range of phone numbers' it notate the 'wireless group' (assigned number).

See attachment

We can obviously remove the dashes considering the format needs to be the same and the 'assigned numbers' changes through the list, 3, 4, 5...

So the equation needs to search for 214

2. Assuming you want formula in column D, beginning at D2, then try something like:

=INDEX(\$M\$1:\$M\$100,MATCH(LEFT(A2,3)&"*",\$N\$1:\$N\$100,0))

where M1:N100 contains the lookup list of Groups vs. Numbers.

3. ## Thank you!

Originally Posted by NBVC
Assuming you want formula in column D, beginning at D2, then try something like:

=INDEX(\$M\$1:\$M\$100,MATCH(LEFT(A2,3)&"*",\$N\$1:\$N\$100,0))

where M1:N100 contains the lookup list of Groups vs. Numbers.

Thank you for this! I tried it and it seems to just return the number '2' each time - am I doing something wrong?

4. I am not sure, in your sample I see only 2 in column M (which seems to apply for area codes 214 and 972). Are there others? Maybe I misinterpreted. You will need to elaborate with more samples and expected results.

5. Originally Posted by NBVC
I am not sure, in your sample I see only 2 in column M (which seems to apply for area codes 214 and 972). Are there others? Maybe I misinterpreted. You will need to elaborate with more samples and expected results.

Yes, I'm sorry - it also looks like some of the text in the original message might have gotten cut off. It was supposed to end with "So the equation needs to search for 214-926-1621 from 'E' in 'N' and if it finds the number, place the corresponding number from 'M' (the wireless group) in 'D'.

In regard to your question, yes...there are different 'assigned numbers'. Please see attached.

6. So we need to find the whole number? I thought you were looking to group by area code...

then try:

=INDEX(\$M\$1:\$M\$100,MATCH(A2,\$N\$1:\$N\$100,0))

7. I'm not sure what you mean by the whole number but it still seems to return only '2'.

Let me try again, column D needs to search column N and if it matches the number then it needs to input the 'wireless group' (whichever number is in column M) into column D. Just to mention again, column M is not just 2's - that's just what you see because it's the top of the screen.

8. And sorry - for some reason I'm not given the option to add a screenshot anymore :/

9. Have you tried my last formula? Just change the A2 to E2 and that is what it is doing. It is searching column N for the phone number you have in E2, and it will return the corresponding group number found in column M.

10. It worked!! For the most part haha! Some return but then some say #N/A?

Page 1 of 3 1 2 3 Last

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•