yogi_Find The Words And The Starting Position(s) Of Words In Cell A2 That Occur In Cells A1 To J1

              Google Spreadsheet   Post  #1854

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Dec-02-2014

post by  amelie Boulliard:
https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/ZEZKIpuBwk4
Spreadsheet function : Search a Cell for a List of Words

In a spreadsheet, I need to Search a Cell for a List of words.

I googled it and found this : 

http://www.myonlinetraininghub.com/excel-search-string-for-a-list-of-words

... which basically explains what I need (except that I need to do this in a spreadsheet, and not in Excel).

But the function they suggest : 

=INDEX(list,SUMPRODUCT(ISNUMBER(SEARCH(list,A2))*ROW($1:$3)))

does not work in my spreadsheet (even after replacing "list" by the cells and "," by ";"

Example : let's say the list of words are in the cells A1:J1

and the cell where I search these words is B2, using their function, I get in my spreadsheet : 

=INDEX(A1:J1;SUMPRODUCT(ISNUMBER(SEARCH(A1:J1;B2))*ROW($1:$3)))

But this doesn't work.

Does anyone know how I can do this?

Thank you

---

Hello James, 

Thank you for trying to help me.

I don't want to share the spreadsheet because of the private information inside, but here is an example : 

	A	B	C	D	E	F	G	H	I	J
1	book	trip	stay	hotel	guest	holiday	B&B	flat	rental	journey
2	This lovely house will make you happy as it is perfect for your holidays.
3	?
4
5
6

the list of words are in the cells A1:J1

and the cell where I search these words is A2

In A3, I want to insert the function analyzing the content of A2 : if the function finds in A2's content at least 1 of the words from the A1:J1 list, I want it to give a value (whatever value : "yes/no" or "1/0" or the number of the 1st caracter where the listed word is found...)

In the above example, I'd like the function to find that A2 contains "holiday" which is in the  A1:J1 list (F1).

I hope this helps :-)

----------------------------------------------------------------------------------------------------------------------------