IF(Contains . . . not returning value I'm actually looking for

PeggyLang

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?

Paul Newcome

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".

Paul Newcome

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

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".

PeggyLang

@Paul Newcome

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

Paul Newcome

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

PeggyLang

@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!

Paul Newcome

Happy to help. 👍️