Find if a cell contains one of series of data

[Andre Croteau]

Hello,



I would appreciate a bit of help...

I have a column of 1000 email addresses say from A1:A1000

I have a series of text cells in a criteria range D13 with the following
data " .com , .net and .org" That criteria range can be extended with
added data.

I would like to have a formula in column B that would show if the cell in
column A contains one of the items in the criteria range (give the results)
, and advise if not in criteria range

A B C
D
1 (e-mail address removed) .com
..com
2 (e-mail address removed) .org
..net
3 (e-mail address removed) "not in list"
..org
4 (e-mail address removed) .com


Thanks in advance

André

 

[Ron Rosenfeld]

Hello,



I would appreciate a bit of help...

I have a column of 1000 email addresses say from A1:A1000

I have a series of text cells in a criteria range D13 with the following
data " .com , .net and .org" That criteria range can be extended with
added data.

I would like to have a formula in column B that would show if the cell in
column A contains one of the items in the criteria range (give the results)
, and advise if not in criteria range

A B C
D
1 (e-mail address removed) .com
.com
2 (e-mail address removed) .org
.net
3 (e-mail address removed) "not in list"
.org
4 (e-mail address removed) .com


Thanks in advance

André

The **array** formula:

=IF(ISNA(MATCH(TRUE,NOT(ISERR(FIND(
CritRng,A1))),0)),"not in list",INDEX(CritRng,
MATCH(TRUE,NOT(ISERR(FIND(CritRng,A1))),0)))

should do what you require.

To enter an **array** formula, after pasting or typing it into the formula bar,
hold down <ctrl><shift> while hitting <enter>. Excel will place braces {...}
around the formula.
--ron

 

[Andre Croteau]

Ron,

Very nice, thank you

André

The **array** formula:

=IF(ISNA(MATCH(TRUE,NOT(ISERR(FIND(
CritRng,A1))),0)),"not in list",INDEX(CritRng,
MATCH(TRUE,NOT(ISERR(FIND(CritRng,A1))),0)))

should do what you require.

To enter an **array** formula, after pasting or typing it into the formula
bar,
hold down <ctrl><shift> while hitting <enter>. Excel will place braces
{...}
around the formula.
--ron

 

[Ron Rosenfeld]

Ron,

Very nice, thank you

André

You're welcome. Glad to help.
--ron