Lookup existence of a cell phone text number

M

MarkMcG

Excel Wizards,

I have a list:

Name Device
-------- ---------
Bill [email protected]
Bill [email protected]
Sue [email protected]
Sue [email protected]
Randy [email protected]
Randy [email protected]

I need to lookup, yes or no, if each unique user has registered any text
device. I know that the device can be registered from 7 different carriers
(vtext.com, txt.att.net, etc).

How to do this in a formula so to get:

Name Text Device
-------- ---------------
Bill No
Sue Yes
Randy Yes

Many thanks, Mark
 
W

willwonka

Give the range of your text providers a range name. I'll use the
range name "data".

The list of your names or in Column A

col a Col b

Bill {=IF(ISNUMBER(FIND(data,C1,1)),"yes","no")}

Make sure that you hit Cntrl-Alt-Enter to make it an array formula
 
W

willwonka

Sorry.. Cntrl-Shift-Enter for array formula.


Give the range of your text providers a range name.  I'll use the
range name "data".

The list of your names or in Column A

col a    Col b

Bill     {=IF(ISNUMBER(FIND(data,C1,1)),"yes","no")}

Make sure that you hit Cntrl-Alt-Enter to make it an array formula









- Show quoted text -
Click to expand...
 
J

JBeaucaire

Expand this list to your full seven options, don't forget the asterisk
wildcard at the beginning of each one:

=IF(SUM(COUNTIF(A1,{"*zmail.biz","*vtext.com","*txt.att.net"}))>0,"Yes","No")

A1 = first email address to check.
 
M

MarkMcG

Thanks will,

This is close.

I have two sheets. One sheet (user with devices) contains the data
(multiple occurrences of a name with several devices) and the other (users)
is just the names. I need to lookup the name in (users) in the list in (user
with devices) and return if the user has registered a text device.

Thanks, Mark
 
J

JBeaucaire

If you post all seven to check, I can adjust this non-array-required formula
to work, else expand it yourself by adding more entries in the section where
I've already put your sample three:

Don't forget the asterisk wildcard at the beginning of each one:

=IF(SUM(COUNTIF(A1,{"*zmail.biz","*vtext.com","*txt.att.net"}))>0,"Yes","No")

A1 = first email address to check.
 
Top