CONTAINS not finding match in second sheet

Looking to create a count of the number of times a name appears. in another sheets column. I keep getting a false result.

=IF(CONTAINS({EE Schedule Range 3}, {EE Schedule Range 2}), "Yes", "No")

Range 3 is with in Range 2 on second sheet. For this example Range 2 is a list of names and Range 3 is one of the cells in that list.

Where am I going wrong.


  • JamesB
    JamesB ✭✭✭✭✭✭

    @Castillo BWIS

    You should be able to use a COUNTIF to get this data.

    =COUNTIF({EE Schedule Range 2},{EE Schedule Range 3})

    EE Schedule Range 2 = Column Range to Search

    EE Schedule Range 3 = Single Name to look for

  • That did work. Returned a number. I then can do a compare to 0.

    Now, Happen to know why did the above IF statement not work?

  • At a little higher level I am trying to get this type of function to work. It still returns nothing. I believe it has todo with the CONTAINS not returning a True value.

    =JOIN(COLLECT({Task Date Range}, {Name List}, CONTAINS("Omer", @cell)), ", ")

  • Think I figured part of the issue out.

    This works:

    =JOIN(COLLECT({Task Date Range}, {Name List}, ="omer"), ", ")

    I think because collect is looking for the "Criterion" Not a true, false statement per say.

    But Contains would be a better solution in my case because "=" looks for a complete match which may not be the case. I also wish to add, If I use a local sheet data range the function I first asked about works. So am I missing something when I use a remote sheet data range without []:[] format?

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!