Posted: 1/3/2014 9:09:05 AM EDT
|
I'm making a distribution list for employees that we can email a SMS message in the event of 2 hour delay, closing, etc... I have a spread sheet with their 10 digit phone numbers (ex 31711114444). In the next column, I have their cell phone carrier (AT&T, Verizon, etc...). I'm trying to use a LOOKUP function to create the addresses (sms phone numbers) to create the distribution list. The lookup table has the following values D1 Verizon E1 @vtext.com D2 AT&T E2 @txt.att.net etc...
Basically, I want to add a 10 digit phone number and cell provider in and have it put the following text behind the number in the next cell. I don't have trouble with joining 2 cells, I just can't get the lookup to work. It keeps dropping a #N/A in the blank cells. Not all employees have provided a cell number. If it's blank, I want the cell to return a blank. Then, I can copy those sms addresses into outlook and email an update. Not sure if I should be using vlookup, lookup, etc... Please help! |
|
I've never used a lookup function in excel, so I'm no help there.
If you want to search just do Ctrl+f you don't need a lookup function. You really should learn access if you don't already know it. Access is for databases, excel is for processing data (not storing, looking up information). |
|
Quoted:
I've never used a lookup function in excel, so I'm no help there. If you want to search just do Ctrl+f you don't need a lookup function. You really should learn access if you don't already know it. Access is for databases, excel is for processing data (not storing, looking up information). I'm not trying to look anything up. I would like to enter the following: 3171114444 and AT&T Then, in another cell it would produce: [email protected] |
|
Quoted:
I'm not trying to look anything up. I would like to enter the following: 3171114444 and AT&T Then, in another cell it would produce: [email protected] Quoted:
Quoted:
I've never used a lookup function in excel, so I'm no help there. If you want to search just do Ctrl+f you don't need a lookup function. You really should learn access if you don't already know it. Access is for databases, excel is for processing data (not storing, looking up information). I'm not trying to look anything up. I would like to enter the following: 3171114444 and AT&T Then, in another cell it would produce: [email protected] You can use a vlookup table along with the concatenate function, or you can use a nested IF statement. ETA - Access is WAY overkill for what you're trying to do. Don't go down that road. |
|
I think the vlookup function will do what you want.
If I understand correctly, you would use the carrier as the variable to go into the array of numbers and carriers, puling out the Verizons, AT&Ts, etc. so that you can append the correct...appendage (  ).
Sorry I'm not more help; the last lookup I did was to use the current date to lookup the appropriate variables for the day and load them into a form. |
|
Quoted:
If you want to get rid of the #N/A display, wrap the vlookup function in an iferror function, such as =iferror(vlookup(b1,lookuptable,2,false),"")This will return a blank ("") if the embedded vlookup function returns an error. Use "&" to join your cells http://i1304.photobucket.com/albums/s527/domoarrigato/xl.gif This is close too, but I don't want to have to enter the "txt.att.net". I just want to enter AT&T and have it do the rest. Reason, is because I have employees with a bunch of different carriers and they are all different. |
|
Quoted: This is really close, but I'm still getting a #N/A if there is no phone number in the cell! This is close... Quoted: Quoted: =B6&VLOOKUP(C6,D1:E2,2,FALSE) B6 is the cell phone number, C6 is the carrier, D1:E2 is the lookup table This is really close, but I'm still getting a #N/A if there is no phone number in the cell! This is close...  |
|
Quoted: This is close too, but I don't want to have to enter the "txt.att.net". I just want to enter AT&T and have it do the rest. Reason, is because I have employees with a bunch of different carriers and they are all different. Quoted: Quoted: If you want to get rid of the #N/A display, wrap the vlookup function in an iferror function, such as =iferror(vlookup(b1,lookuptable,2,false),"")This will return a blank ("") if the embedded vlookup function returns an error. Use "&" to join your cells http://i1304.photobucket.com/albums/s527/domoarrigato/xl.gif This is close too, but I don't want to have to enter the "txt.att.net". I just want to enter AT&T and have it do the rest. Reason, is because I have employees with a bunch of different carriers and they are all different. |
|
Quoted:
Sorry I wasn't clear. The txt.att.net was returned by the vlookup function, based on the AT&T entered in the prior column. That's also where the iferror function is pu to use. Quoted:
Quoted:
Quoted:
If you want to get rid of the #N/A display, wrap the vlookup function in an iferror function, such as =iferror(vlookup(b1,lookuptable,2,false),"")This will return a blank ("") if the embedded vlookup function returns an error. Use "&" to join your cells http://i1304.photobucket.com/albums/s527/domoarrigato/xl.gif This is close too, but I don't want to have to enter the "txt.att.net". I just want to enter AT&T and have it do the rest. Reason, is because I have employees with a bunch of different carriers and they are all different. That worked! Thank you guys, that was very helpful. |