I have a table in calc like this
col1 col2
loo coo
moo
coo
too
I want to test if col2 is in col1.
If I use =ISNUMBER(SEARCH(B2,A4)), it returns TRUE. And that’s great.
BUT if I use =ISNUMBER(SEARCH(B2,A2:A5)), it returns FALSE
I even tried defining a name for the range but that returns FALSE as well.
What am I doing wrong? Is there a better way of doing this search?
Thanks for any assistance.
Cheers.
SEARCH() will examine the contents of one cell. If given a cell range, it will examine the top left cell in the range a single cell in the range, positioned adjacent to the formula (intersecting row or column).. It returns the character position inside the examined cell where the search string starts, if string is found.
I guess the MATCH() function suits your requested purpose better. That function returns the cell number from a cell range where a match is found.
edit: Description of SEARCH() behavior corrected. Thanks @mikekaganski!
I suppose it will use a cell in the range found by intersection, rather than “top left”?
Thanks for this - very helpful.
With your input, I changed the function to the following and it seems to be working a treat:
=ISNUMBER(MATCH(B2, valuelist,0)) ==> where valuelist is a defined range
I believe where I went wrong was following the example using SEARCH from this link: https://www.libreofficehelp.com/check-if-cell-contains-specific-text/
But the modified function using MATCH works very well.
Thanks again for this assistance - much appreciated! 
Unfortunately, the text matching spreadsheet functions are too complex. They depend on a bunch of extra options and these extra options are stored separately with each document.
In the attached document the formulas in D1, E1 and F1 try to match the single letter in C1 within the list A2:A9. Why do they all fail with error #na ?
Call menu:Tools>Options>Calc>Calculation
I checked option “Search criteria = and <> match whole cells” and disabled all wildcard matching. I think this is what most users want in most practical use cases. You want to match the content of one cell including any character.
If you uncheck the “… Match whole cell” option, D1 returns the correct match.
If you check wildcard matching, E1 returns the correct match.
If you check regex matching, F1 returns the correct match.
However, these options are not exactly alternative to each other. Wildcard matching and regex matching impose some additional issues with special characters.
With wildcard matching, ? does not match anywhere. A correct match requires that the special character ? is escaped with a tilde: ~?
With regex matching, ^h does not match anywhere. A correct match requires that the special character ^ is escaped with a backslash: \?
All this text searching and matching is a can of worms.
t75945.ods (12.7 KB)
My suggestion for a solution that should work for most use cases:
… and if you aim for compatibility with Excel, use wildcard matching (which is not possible to be disabled in Excel, which made us to make wildcards the default in 5.3).