## Tuesday, December 2, 2014

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

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Dec-02-2014
post by  amelie Boulliard:
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 :
... 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 :-)
----------------------------------------------------------------------------------------------------------------------------