# How to check if a cell contains any of the values of another range of cells?

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 )

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.

See VLOOKUP and HLOOKUP.

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.

``````=IF(AGGREGATE(9;6;SEARCH(B1:B3;A1))>0;"YES";"NO")
``````

Example.ods (9.4 KB)

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:

• A1 contains: Evil Character
• B1 to B3 contains the values the content of A1 shall be compared to: B1: Hero, B2: Evil Character, B3: DAC

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)

Match â€śunderstandsâ€ť wildcards.
Could be so:

``````=ISNUMBER(MATCH("*"&A1&"*";B1:B3;0))
``````

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()

1 Like

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!