I'll try to describe all the relevant information...

Column A - contains comment fields, randomly containing keyword(s)
Column B - contains the unknown search formula, yet to be determined
RangeXYZ - contains a list of multiple keywords

I wants the formula in B2 to search the comment field A2 using the entire list of keywords found in RangeXYZ. The general assumption can be made that a comment field should only contain one keyword. I'd like A2 to result in a blank or "NA#" if nothing was found... or the keyword, if any keyword was found.

For example:
A2 = "The customer was happy about their widget."
B2 = Unknown search formula, resulting in "widget"
RangeXYZ contains = widget, newspaper, sports car, computer, calculator

I hope that I provided enough information...

Thanks for any help or insight...

Hi Malvaro,

Assume your list of keywords is in a single column:

=IF(MATCH(FALSE,ISERR(SEARCH(J1:J3,A1)),0)0,INDEX (J1:J3,MATCH(FALSE,ISERR(SEARCH(J1:J3,A1)),0),0),N A())

This is an array formula so it must be entered by pressing Shift+Ctrl+Enter,
not Enter. The range which you call XYZRange is J1:J3 in the above formula.

--
Cheers,
Shane Devenshire


"Malvaro" wrote:


I'll try to describe all the relevant information...

Column A - contains comment fields, randomly containing keyword(s)
Column B - contains the unknown search formula, yet to be determined
RangeXYZ - contains a list of multiple keywords

I wants the formula in B2 to search the comment field A2 using the
entire list of keywords found in RangeXYZ. The general assumption can
be made that a comment field should only contain one keyword. I'd like
A2 to result in a blank or "NA#" if nothing was found... or the
keyword, if any keyword was found.

For example:
A2 = "The customer was happy about their widget."
B2 = Unknown search formula, resulting in "widget"
RangeXYZ contains = widget, newspaper, sports car, computer,
calculator

I hope that I provided enough information...

Thanks for any help or insight...




--
Malvaro

Try this:

=LOOKUP(1E100,SEARCH(F$2:F$7,A2),F$2:F$7)

Where F2:F7 is your list of keywords.

a comment field should only contain one keyword.

If a cell contains more than one keyword the result will be the matched
keyword listed *last* in the range F2:F7.


--
Biff
Microsoft Excel MVP


"Malvaro" wrote in message
...

I'll try to describe all the relevant information...

Column A - contains comment fields, randomly containing keyword(s)
Column B - contains the unknown search formula, yet to be determined
RangeXYZ - contains a list of multiple keywords

I wants the formula in B2 to search the comment field A2 using the
entire list of keywords found in RangeXYZ. The general assumption can
be made that a comment field should only contain one keyword. I'd like
A2 to result in a blank or "NA#" if nothing was found... or the
keyword, if any keyword was found.

For example:
A2 = "The customer was happy about their widget."
B2 = Unknown search formula, resulting in "widget"
RangeXYZ contains = widget, newspaper, sports car, computer,
calculator

I hope that I provided enough information...

Thanks for any help or insight...




--
Malvaro

Thanks much! :)

You're welcome!

--
Biff
Microsoft Excel MVP


"Malvaro" wrote in message
...

Thanks much! :)

T. Valko;687679 Wrote:
Try this:

=LOOKUP(1E100,SEARCH(F$2:F$7,A2),F$2:F$7)

Where F2:F7 is your list of keywords.
-
a comment field should only contain one keyword.-

If a cell contains more than one keyword the result will be the matched

keyword listed *last* in the range F2:F7.


--
Biff
Microsoft Excel MVP


"Malvaro" wrote in message
...-

I'll try to describe all the relevant information...

Column A - contains comment fields, randomly containing keyword(s)
Column B - contains the unknown search formula, yet to be determined
RangeXYZ - contains a list of multiple keywords

I wants the formula in B2 to search the comment field A2 using the
entire list of keywords found in RangeXYZ. The general assumption
can
be made that a comment field should only contain one keyword. I'd
like
A2 to result in a blank or "NA#" if nothing was found... or the
keyword, if any keyword was found.

For example:
A2 = "The customer was happy about their widget."
B2 = Unknown search formula, resulting in "widget"
RangeXYZ contains = widget, newspaper, sports car, computer,
calculator

I hope that I provided enough information...

Thanks for any help or insight...




--
Malvaro -




--
Malvaro

Curious? Is there any way to show all the keywords found? If more than one happens to be found?