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

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 crosssheet 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
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 61.4K Get Help
 325 Global Discussions
 183 Industry Talk
 418 Announcements
 4.2K Ideas & Feature Requests
 127 Brandfolder
 154 Just for fun
 124 Community Job Board
 441 Show & Tell
 26 Member Spotlight
 1 SmartStories
 278 Events
 34 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!