Hi there,
I tried this example but it does not seem to work (at least not for me).
I want to check whether a cell contains any of the values of another range of cells. Alternativly I can also check whether a cell contains any of some given keywords.
Any idea how to best achieve this? (ChatGPT was no real help yet 
)
Thanks in advance!
Whenever you try to do anything like this (100% text, no arithmetics) in a spreadsheet, you are on the wrong track.
It can be done with a most simple database: word_lookup.odb (29.4 KB)
Open the forms to view and edit word lists and sentences.
Open the reports to get sentences and contained words in print layout.
[Edit: wrong answer, not a solution to the question that I didn’t understand like it was meant.]
To lookup the value of cell A2 in a one column vector range B2:B99 you can use
=VLOOKUP(A2;B2:B99;1;0)
It displays either the value itself, or #N/A if not found. To display TRUE instead of the value if found compare the return value to the value itself
=VLOOKUP(A2;B2:B99;1;0)=A2
Use HLOOKUP() to lookup values in a one row vector range.
Thank you. If I understand the lookup commands correctly, this might maybe not work. Here is an example:
Let’s say in A1 there is the value “Artifact/Evil Character” and in another cell B1 there would be a list of Strings (do not know the correct separation and escaping yet): “Hero, Evil Charater, DAC”. Now I want to know whether at least one of the Strings is contained in A1.
Alternativly I could do: Same in A1 and in B1 to B3 I would do: B1=Hero, B2=Evil Character, B3=DAC. Then I would need to know whether at least any of the values in B1 to B3 is contained in A1.
So either I got the examples for VLOOKUP and HLOOKUP wrong or they don’t really help in this.
Thank you. Do not yet know the AGGREGATE function. But I really wonder why this works since when I just try =SEARCH(B1:B3;A1) I always get an error: #value - But only when I use strings with included Whitespace like Evil Character. How would I have to adjust the strings for the search functions to work when they contain whitespaces?
Meanwhile I also found another solution from here. I just had to activate to allow Regular Expressions in my formulas and then could do the following:
=SUCHEN(“Evil Character|DAC”;A1) and this works too. Unfortunately this is harder to extend later on in case a new string needs to be added since I use that formula in a list with thousends of entries.
Maybe I can find sth. for this in addition to just concatenate that string out of a keyword column or so.
=SEARCH(A1;B1) returns the position number where the text in cell A1 can be found in the single cell B1 or #VALUE! if not at all.
=FIND(A1;B1) does the same case-sensitively.
=MATCH(A1;B1:B99;0) returns the position number where the whole value of cell A1 can be found in the column B1:B99 or #N/A! if not at all.
If you are not interested in the position number, you can test if the function returns a number or not: =ISNUMBER(MATCH or FIND or SEARCH(…))
Thank you. This I know. The only issue is that SEARCH struggles in cases where some of the strings that shall be checked contains a whitespace. For this I did not yet find a good solution.
For example:
When I now do =SEARCH(B1:B3;A1) I get the #value error. If I change the value of A1 to Hero it works so the white space in B2’s value or in A1’s value (when it was set to Evil Character) makes the trouble here.
So far I now use this workaround: =SEARCH(TEXTJOIN("|";1;"""";$B$1:$B$3;"""");A1) - and as of now it is working fine.
SEARCH is not the right function. Use MATCH instead. Call Tools>Options>Calc>Calculation and turn off “Match whole cell”
96503.ods (15.5 KB)
Why do you think SEARCH is not the correct one? At least it also works ATM.
SEARCH compares 1 value with 1 other value.
MATCH compares 1 value with a vector of cells (1 row or column).
In order to match the single value in parts of the vector, you can use patterns or turn off the “whole cell” option. Both options are configurable in the options dialog.
Thank you for the explanation!
Currently I also try to check of the cell I am looking at starts with the given string and using an AND()-function. But it seems AND() evaluates all arguments even though the first one already provides FALSE as result (the second one then evaluates to a #value error so the entire function results in that error unfortunately - was hoping AND() is working optimised here). So need to check for something else.
Use ISNUMBER.
I did and is ISNUMBER is returning FALSE as first argument of AND I was of the opinion AND finishes checking the subsequent arguments. But it still seem to check them all. Now I switched to nested IFs.
Right. This is because when written this way, the formula is perceived as =SEARCH(B1;A1) or for your data =SEARCH("Hero";"Evil Character"). Try setting A1 to Hero and this formula will work.
If you enter the same formula as an array formula (with Ctrl+Shift+Enter instead of the usual Enter), you will get an array of three #VALUE! errors. This is because you will actually get three formulas like =SEARCH(B1;A1), =SEARCH(B2;A2) and =SEARCH(B3;A3), and there are no values to search in cells A2:A3.
But if you enter {=SEARCH(B1:B3,$A$1)}, you will get an array like
#VALUE!
1
#VALUE!
Should not be doing that. Just take my word for it - more than two IF() in one formula is overkill. This formula is very difficult to read, understand and, if necessary, correct. Just go back to the formula with AGGREGATE()
Thank you. Maybe I need to look at the aggregate function then. As for nested IFs I openened a thread in German but this is for efficiency. Getting the #value error is not the problem since that is what I expect and what I wanted to utilise within the AND().
So one question still is left: Does AND() evaluate all of its arguments even though the first one already resolves to FALSE? For me it looks like that.
A simple test will instantly answer this question of yours
https://help.libreoffice.org/7.5/en-US/text/scalc/01/func_aggregate.html
This function returns an aggregate result of the calculations in the range, In your case I used the sum function, so add the result of all the functions =SEARCH(B1;A1), =SEARCH(B2;A1) and =SEARCH(B3;A1).
Results list:
#VALUE!
1
#VALUE!
The sum of the results is 1, therefore greater than zero, this means that a match has been found.
The Aggregate function enables you to omit hidden rows, errors, SUBTOTAL and other AGGREGATE function results in the calculation.
A simple test will instantly answer this question of yours
Yep, as expected. What a pitty. Was hoping it is “optimised”.
.
.
@gaetanopr
Thank you for the detailed explanation. It even seems to work with strings containing whitespaces. That is a real clever approach!