CONTAINS not returning results
I'm having an ongoing problem with using CONTAINS to count the occurrence of a value in a multiselect column.
Here is my sample data:
Here is the formula:
=IF(CONTAINS("East", [Column2]:[Column2]), "Yes", "No")
As you can see, the first and secondtolast row should return "Yes" and the rest should return No, but they are all returning "Yes"
I'm pretty sure I have the syntax correct, but I could be missing something
Thanks for any help you can provide.
Best Answer

Hi,
When you write this kind of expression it will look for all values in the column, not only the current row:
Column7]:[Column7]
The correct formula would look like :
IF(CONTAINS("test", [Column7]@row),1,0)
Answers

Hi,
When you write this kind of expression it will look for all values in the column, not only the current row:
Column7]:[Column7]
The correct formula would look like :
IF(CONTAINS("test", [Column7]@row),1,0)

Thank you! That did work. Unfortunately, that's the first layer of my problem. If I wanted to look up those values from another sheet, using a reference range, how do I build the @row portion into it?
I can do a helper column on the source sheet and then just search for "Yes", but I'd like to avoid a separate column for each value.
Thanks again.

having a helper column to store the current row number and then using the index formula should to the trick.
Help Article Resources
Categories
Check out the Formula Handbook template!