It works!! Greatly appreciate your help!! THANKS A LOT!!
However, I still have confusions about SUMPRODUCT.
For the following two formulas,
(A) SUMPRODUCT(--ISNUMBER(SEARCH(CheckList,B2))),
it seems that they can produce the same result.
But I doubt how formula (A) works.
For example,
Case 1: =sumproduct(1,0,0,0) returns 0 (these 4 numbers are multiplied to
get 0),
Case 2: =sumproduct({1,0,0,0},{1,0,0,0}) returns 1 (these 8 numbers are
muliplied first, then summed to get 1).
I want to know whether formula (A) can be reduced to Case 1 formula. If so,
the result will always be zero as long as there exists one unmatched word on
the checklist.
Sorry, I am really confused...I have searched a number of websites but none
of them supply me appropriate explanations...Can you explain this strange
thing? Please help!! Thanks!! (coz i not only want to solve the problem, but
also want to know how and why it is solved....to learn things.)

BTW, for the dynamic range checklist, should the formula be
=OFFSET(Sheet2!$E$2,0,0,COUNTA(Sheet2!$E:$E)-1,1).
when there is a heading on top of the checklist?

Many thanks again!!
(Really get a lot of prompt and constructive replies in this newsgroup.)

"Ron Coderre" ...
Minor correction...
The formula for C2 and below should be:
=IF(SUMPRODUCT(--ISNUMBER(SEARCH(CheckList,B2))),"FOUND","NOT FOUND")

(I wasn't paying good attention when I pasted the wrong version in my
post)

***********
Regards,
Ron

XL2002, WinXP-Pro


"Ron Coderre" wrote:

First
Put this formula in cell C2 and copy it down as far as you need.
=IF(SUMPRODUCT(--ISNUMBER(SEARCH(CheckList,B2))*1)0,"FOUND","NOT FOUND")

Second
Create your list of forbidden words in a blank column. For this example,
I
started my list in Cell E2 on Sheet2.

Then, InsertNameCreate
Name in Workbook: CheckList
Refers to: =OFFSET(Sheet2!$E$2,0,0,COUNTA(Sheet2!$E:$E),1)
Click [OK]

That created a dynamic range name called CheckList that automatically
resizes to accomodate the number of items in the list beginning in cell
E2 on
Sheet2.

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"guy" wrote:

http://cjoint.com/data/bguHFjhv8r.htm

My problem is attached on the above link. Anyone can help? I have been
looking for solutions for long....to make a simple search engine.

Also, can I make Column C update automatically (show Found/NOT Found)
when I
keep adding new TargetNames on Column B.

Appreciate any help from you!!
THANK YOU VERY MUCH!!!