IF and CONTAINS formula issue

I want my formula to tell me if the “name” contained in a cell of the current sheet is present in a contact list column from another sheet (could be only once or several times).

These formulas give me a “No”:

=IF(CONTAINS(Name1,{123 Plan Assigned To}) "Yes", "No")

=IF(CONTAINS(“Name X”,{123 Plan Assigned To}) "Yes", "No")

But if I use this formula, I get a “Yes”:

=IF((COUNTIF({123 Plan Assigned To}, =Name1)) > 0, "Yes", "No")

Why the CONTAINS formula is not working?

Thx :)

Find more posts tagged with

Formulas

Accepted answers

All comments

Kelly Moore

Hey @MEL42

The "No" in your first formulas is not because of the CONTAINS but from the syntax in using an IF formula with cross sheet references. As you just found out, many functions do not work directly in a cross-sheet IF statement as many don't allow the inclusion of both a range (the cross sheet reference range) and the criteria. The COUNTIFS function is one of the function that does allow the range and criteria. You could include the CONTAINS function (if its needed?) in the countifs. The 'search within' element is '@cell'

Does that help?

Kelly

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.