How can I see if a cell contains a keyword from a list?

G

Guest

I want to determine if a keyword from a defined list can be found in a cell.
Let the following be my key words:

Keyword List

Excel
Access
Word
Water
Air
Fire
Earth

My cells are

Column A

MS Excel
Microsoft Acess
Access97
Excel2000
JDE
SAP
Earth, Wind, & Fire
etc...

In this example, I would want a function that returns a value "x" in
adjacent cells to column A if it can find a word from the keyword list in the
A cell. How can I do this?

Any help is greatly appreciated!
 
G

Guest

Where your keywords are in A1:A7 and F1 is the text to search, try:

=IF(SUMPRODUCT(--(ISNUMBER(SEARCH(A$1:A$7,F1)))),"X","")
 
G

Guest

Try something like this:

With
A1:A7 containing your posted list of value to test
And
F1:F7 containing the list of keywords

This formula returns "X" if A1 contains any of the keywords
B1: =IF(SUMPRODUCT(COUNTIF(A1,"*"&$F$1:$F$7&"*")),"X","")

Copy that formula down as far as you need.

Does that help?
***********
Regards,
Ron

XL2002, WinXP
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

The best way to create a Keyword Seach field in excel 1
Finding a single word match in a text string and assigning a code 8
producing a report via a keyword - filtering and macros not suitab 1
How can I generate a Tag Cloud of words in Excel? 2
Macro - reads cells in a column .If keyword found moves cell conte 5
Excel If a Cell Contains a Certain Text (From Another Table) 0
a data validation list that contains a list of named ranges - not alist from a named range 1
Formatted cell column in drop down list 1

Top