Does anything in range A appear in column B?

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

Find more posts tagged with

Formulas

References

IF(

Accepted answers

All comments

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 ACT

@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!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.