Page 1 of 3 1 2 3 LastLast
Results 1 to 10 of 22

Thread: Formula Help

  1. #1

    Formula Help



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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
    Attached Thumbnails Attached Thumbnails Click image for larger version. 

Name:	Excel Example.png 
Views:	13 
Size:	15.5 KB 
ID:	3499  

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,508
    Articles
    0
    Excel Version
    Excel 2016
    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. #3

    Thank you!

    Quote Originally Posted by NBVC View Post
    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. #4
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,508
    Articles
    0
    Excel Version
    Excel 2016
    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. #5
    Quote Originally Posted by NBVC View Post
    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.
    Attached Thumbnails Attached Thumbnails Click image for larger version. 

Name:	Excel Example.png 
Views:	3 
Size:	15.5 KB 
ID:	3500  

  6. #6
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,508
    Articles
    0
    Excel Version
    Excel 2016
    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. #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. #8
    And sorry - for some reason I'm not given the option to add a screenshot anymore :/

  9. #9
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,508
    Articles
    0
    Excel Version
    Excel 2016
    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. #10
    It worked!! For the most part haha! Some return but then some say #N/A?

Page 1 of 3 1 2 3 LastLast

Posting Permissions

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