CONTAINS not finding match in second sheet

Castillo BWIS

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

@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

Castillo BWIS

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?

Castillo BWIS

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

Castillo BWIS

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?