Does anything in range A appear in column B?

Kristen R. Stewart

I am trying to set up a formula that lets me know if any of the Project Tittles in one sheet appear in any comments on a separate sheet. When the function should return "True", I get "#invalid data type"

=IF(CONTAINS({Project Tittle Range 1}, [Comments]:[Comments]), "True", "False")

Leibel S

Try something like this:

=IF(COUNTIFS({Project Tittle Range 1}, HAS([Comments]:[Comments], @cell)) > 0, "True", "False")

Kristen R. Stewart

@Leibel S , that did not work either :(

Leibel S

What doesn't work? You get an error?

Kristen R. Stewart

Formula returned false when it should have returned true (i know there is a project tittle in the comments)
I think the issue stems from either asking it to use a range as the reference and/or that I'm asking it to identify if a word appears in a paragraph

Matt Lynn#

@Kristen R. Stewart I just tested and it works fine. Your original formula is the same I used and it works, but I agree your search value being a range is the problem. You can't search a range with a range. Rather than [Comments]:[Comments], just use Comments@row which would be one search value/cell.
Also since your comments column is one word without a space you shouldn't need the [] brackets. Even if you needed the range it should just be comments:comments no brackets unless you have a space in the column name.

Leibel S

@Kristen R. Stewart

You can switch to a CONTAINS function. Please note that this may bring up false positives.

For example if your {Project Tittle Range 1} has A Project Title called "Project 1" and [Comments]:[Comments] "Project 10" It will still return true.

=IF(COUNTIFS({Project Tittle Range 1}, CONTAINS(@cell ,[Comments]:[Comments])) > 0, "True", "False")

Kristen R. Stewart

@leilel S, you're probably right that I am getting a significant number of false positives.
I will have to try something else.
Thank you All!