IF and CONTAINS formula issue

MEL42

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

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