Formula Help

M

mpourjavad

New member
Joined
May 12, 2015
Messages
14
Reaction score
0
Points
0
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
 

Attachments

  • Excel Example.png
    Excel Example.png
    15.5 KB · Views: 17
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!

NBVC said:
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.
Click to expand...

Thank you for this! I tried it and it seems to just return the number '2' each time - am I doing something wrong?
 
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.
 
NBVC said:
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.
Click to expand...

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.
 

Attachments

  • Excel Example.png
    Excel Example.png
    15.5 KB · Views: 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))
 
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.
 
And sorry - for some reason I'm not given the option to add a screenshot anymore :/
 
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.
 
It worked!! For the most part haha! Some return but then some say #N/A?
 
Actually I spoke too soon :/ I just filled down and I'm getting numbers that do not have the correct corresponding number?
 
Try attaching an actual spreadsheet example instead of a picture. The matches need to be exact matches... ie. dashes, spaces, etc need to be the same on both sides.
 
NBVC said:
Try attaching an actual spreadsheet example instead of a picture. The matches need to be exact matches... ie. dashes, spaces, etc need to be the same on both sides.
Click to expand...

Here you go! I figured they have to match so I made a third tab removing the dashes and am trying the formulas on this one. Please see attachment.
 

Attachments

  • Excel Example.png
    Excel Example.png
    15.5 KB · Views: 8
Is there a formula? When I open the image it's not showing me one...
 
You need to attach an .xls or .xlsx file, not an image file.
 
Sorry! I thought I had done that...here you go!
 

Attachments

  • Copy of ATT Charges.xlsx
    56.7 KB · Views: 4
Ok, assuming you are looking at the phone numbers in column A, try this:

=IF(A2="","",INDEX(M:M,MATCH(A2,N:N,0)))

copied down.

the first part will check if there is anything in column A first. IF not it will return a blank, otherwise it will do the search and find.... note, your database is larger than 100 rows, so i just used whole column references to make sure to encapsulate all options.

I see more results now.
 
NBVC said:
Ok, assuming you are looking at the phone numbers in column A, try this:

=IF(A2="","",INDEX(M:M,MATCH(A2,N:N,0)))

copied down.

the first part will check if there is anything in column A first. IF not it will return a blank, otherwise it will do the search and find.... note, your database is larger than 100 rows, so i just used whole column references to make sure to encapsulate all options.

I see more results now.
Click to expand...
I'm looking at the phone number in 'E' and if they match a phone number in 'N' then I want to the formula to generate what number is in 'M'...I'm not sure how better to explain it sorry. Let me give you an example based on the spreadsheet I sent you and the new image I have sent. The formula needs to read the number in E10 and then search column N and when it finds its' match, then take the assigned number from M128 (in this case - 5) and places it in D10. Hope this helps!
 

Attachments

  • Excel Example D.png
    Excel Example D.png
    28.6 KB · Views: 3
They are not exact matches because you are missing a dash after the area code...

Try:

=IF(A2="","",IFERROR(INDEX(M:M,MATCH(SUBSTITUTE(E2," ","-"),N:N,0)),""))
 
NBVC said:
They are not exact matches because you are missing a dash after the area code...

Try:

=IF(A2="","",IFERROR(INDEX(M:M,MATCH(SUBSTITUTE(E2," ","

You got it!!!! If I needed to remove the dashes I could but this seems to work. Thank you so much!!
Click to expand...
 
You must log in or register to reply here.
Back
Top