Formula to extract a word from a list of words

[Hafeez1982]

I have list of words in a range. I would like to create a formula to see if those words are within the cell value (A9-A12). I would like the formula to provide the actual word or return an error if no word is found or if two more of the words from the list are found in the same cell. (B9-B12)

It does not have to be case sensitive

Using office 355

I have found a similar thread but have been asked to post a new thread as new question

Formula to find words from a list in cell | Page 2 | MrExcel Message Board

 

[shift-del]

Mappe19
	G
9	an error
Tabelle1

Cell Formulas
Range	Formula
G9	G9	=LET( z,$A$1:$A$2, a,--ISNUMBER(SEARCH(z,A9)), b,SUM(a), c,1/a, d,LOOKUP(42,c,z), result,IF(b=1,d,"an error"), result)

 

[Cubist]

How did Cloud AWS became Data Quality Issue?

 

[Hafeez1982]

How did Cloud AWS became Data Quality Issue?

=LET( z,$A$1:$A$2, a,--ISNUMBER(SEARCH(z,A9)), b,SUM(a), c,1/a, d,LOOKUP(42,c,z), result,IF(b=1,d,"an error"), result)

thanks for this but solution does not work , all I get is "an error" in the each of the cells when spilling down.

 

[shift-del]

My formula does not spill.