Search for specific words in cells, add true or false if even one is present

Question

So I've googled the internet, but can't seem to find a proper function.

I have cells containing some Categories and in each cell, they're different.

Example:

1. Art, History
2. Architecture, Design, Art, Magic
3. Photography, Design

What I'm after is this, that if a cell contains even one word from a specific list, then it would add a true or false next to another cell.

I know there's a function to find a specific word from a cell and then true or false it:

=IF(ISNUMBER(SEARCH("Art";K3));"true";"false")

And I also found something that I need, but this doesn't seem to be working in openOffice:

=IF(COUNT(SEARCH({"Architecture","Magazines","Movie","Music","Photography","Theory"},K2)),"true","false")

How can this be done?

Answer 1

Your second formula will work also in Excel only as array formula. So even in openoffice an array formula is needed.

The formula is:

{=IF(SUM(NOT(ISERROR(SEARCH({"Architecture","Magazines","Movie","Music","Photography","Theory"},K2))))>0,"true","false")}

To enter an array formula in openoffice, enter the formula first and click fx then. In the Function Wizard dialog check the [x] Array checkbox.

If you fill array formulas downwards with the cell handle, then you will create a range array. This is not the goal in this case. Thats why do not fill with the cell handle. Copy the formula and paste it into the other cells.