Find if a cell contains one of series of data
 

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 D1:D3 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 .com
..com
2 .org
..net
3 "not in list"
..org
4 .com


Thanks in advance

André

Find if a cell contains one of series of data
 

On Sun, 15 Oct 2006 11:47:05 GMT, "Andre Croteau"
wrote:

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 D1:D3 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 .com
.com
2 .org
.net
3 "not in list"
.org
4 .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

Find if a cell contains one of series of data
 

Ron,

Very nice, thank you

André


"Ron Rosenfeld" wrote in message
...
On Sun, 15 Oct 2006 11:47:05 GMT, "Andre Croteau"

wrote:

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 D1:D3 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 .com
.com
2 .org
.net
3 "not in list"
.org
4 .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

Find if a cell contains one of series of data
 

On Sun, 15 Oct 2006 14:32:13 GMT, "Andre Croteau"
wrote:

Ron,

Very nice, thank you

André

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