CONTAINS not returning results

I'm having an ongoing problem with using CONTAINS to count the occurrence of a value in a multi-select column.

Here is my sample data:

Here is the formula:

=IF(CONTAINS("East", [Column2]:[Column2]), "Yes", "No")

As you can see, the first and second-to-last row should return "Yes" and the rest should return No, but they are all returning "Yes"

I'm pretty sure I have the syntax correct, but I could be missing something

Thanks for any help you can provide.

Find more posts tagged with

Accepted answers

Etienne Mermillod

Hi,

When you write this kind of expression it will look for all values in the column, not only the current row: Column7]:[Column7]

The correct formula would look like :

IF(CONTAINS("test", [Column7]@row),1,0)

All comments

Etienne Mermillod

Hi,

When you write this kind of expression it will look for all values in the column, not only the current row: Column7]:[Column7]

The correct formula would look like :

IF(CONTAINS("test", [Column7]@row),1,0)

AFlint

@Etienne Mermillod

Thank you! That did work. Unfortunately, that's the first layer of my problem. If I wanted to look up those values from another sheet, using a reference range, how do I build the @row portion into it?

I can do a helper column on the source sheet and then just search for "Yes", but I'd like to avoid a separate column for each value.

Thanks again.

Etienne Mermillod

having a helper column to store the current row number and then using the index formula should to the trick.

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.