IF(Contains . . . not returning value I'm actually looking for
I have the following 'IF(CONTAINS . . . formula
which is returning '#UNPARSEABLE'.
What I need is the Names of individuals where #0136 appears in a column on another worksheet.
I don't really know if this is the correct formula to be using for the action I'm wanting.
Thoughts?
Best Answers
-
You would need a JOIN/COLLECT instead.
=JOIN(COLLECT({Name Column}, {Store Column}, HAS(@cell, [Lookup value]1)), CHAR(10))
You should apply text wrapping to whatever cell you put the above in. If you want a different delimiter other than a line break between names, replace CHAR(10) with "delimiter of choice".
-
It wouldn't be an AND function. It would just be another range/criteria set within the COLLECT function.
=JOIN(COLLECT({Name Column}, {Store Column}, HAS(@cell, [Lookup value]1), {Week Number Column}, HAS(@cell, [Lookup Value]10)), CHAR(10))
Answers
-
You would need a JOIN/COLLECT instead.
=JOIN(COLLECT({Name Column}, {Store Column}, HAS(@cell, [Lookup value]1)), CHAR(10))
You should apply text wrapping to whatever cell you put the above in. If you want a different delimiter other than a line break between names, replace CHAR(10) with "delimiter of choice".
-
YOU ARE A FORMULA WIZARD!!! THAT is amazing. I don't think I would ever have gotten there.
ok; one more hurdle - can I add 'AND' into that formula?
For instance I need to be able to pull ONLY 'Kenny Powell' because Lookup worksheet needs tech servicing Store #0136 ON Week #4
-
It wouldn't be an AND function. It would just be another range/criteria set within the COLLECT function.
=JOIN(COLLECT({Name Column}, {Store Column}, HAS(@cell, [Lookup value]1), {Week Number Column}, HAS(@cell, [Lookup Value]10)), CHAR(10))
-
@Paul Newcome I have been trying every combination I could think of but I was always including AND.
Again, FORMULA WIZARD!!!! Can't thank you enough!!! Have a great weekend!
-
Happy to help. 👍️
Help Article Resources
Categories
Check out the Formula Handbook template!
