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
Check out the Formula Handbook template!