Excel Search a Cell for a List of Words

[aatral]

how we could search one cell and compare it to a list of words. If any of the words in the list existed then return the matching word.

[Glenn Kennedy]

A sample sheet would have been nice... but:

=IFERROR(LOOKUP(1000,SEARCH(E$1:E$5,A2),E$1:E$5),"")

where E1:E5 contain keywords and A2 is the cell being searched.

[aatral]

Hi Glenn,
Thanks for pitching in.

While trying the above formula,

It shows up with the value "0", where I need a result as a text string.

I also tried with the below conditions,

=INDEX(List,SUMPRODUCT(ISNUMBER(SEARCH(List,D3))*ROW(J$1:J$60)))

and it resulted in #NA.

Also tried with using wildcard

SEARCH("*"&D3&"*",List)

the results are same. #NA



Sample data is attached to this thread.

[AliGW]

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not told us about this. You are required to do so.

Please see Forum Rule #3 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important: https://excelguru.ca/a-message-to-forum-cross-posters/

(Note: this requirement is not optional. As you are new here, I shall do it for you this ONCE: https://chandoo.org/forum/threads/ex...f-words.54074/)

[Glenn Kennedy]

You used a formula range that also included blank cells.

=IFERROR(LOOKUP(1000,SEARCH(J$2:J$41,D2),J$2:J$41),"")

If you had actually used the real range of keywords (I said "where E1:E5 contain keywords") it works perfectly, or

=IFERROR(LOOKUP(1000,SEARCH(J$2:J$41,D2),J$2:J$41),"Not Avaialable")

if you prefer.

[aatral]

Thanks for your support Glenn, the issue is resolved.

[Glenn Kennedy]

No problem!!