Help with an IF(CONTAINS formula

Options

Hello,

I'm trying to create a formula that pulls company names from a master into a separate if another column contains a certain project.

Here are the columns from the master. The Project/s or Relationship Column is a multiselect.

Here is the formula so far - =IF(CONTAINS("GO 2", {Projects}), ""{Company}","" )

The range Projects is all of the Project/s or Relationships column in my master and Company is the first row of the Company Name column in my master. Once I get the formula, I was going to make it a column formula. I'd like the formula to only return companies where "GO 2" is part of the list of projects.


Thanks!

Answers

  • Purnima Gore_PGPS
    Options

    Hi @HComenetz

    Could you show us what it would look like using the columns as you see them working please?

    This example doesn't show me what the output should look like so in order to provide an example, it would help to see what it looks like in that column when it has "GO 2" and when it doesn't.

    Thanks

    Purnima

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    You are going to need a helper column that has numbers manually entered in it. You will start at 1 and then go down however far you think you will need (plus a suggested buffer). So if you think you should have no more than 50 being pulled over, I suggest going down to 75 just to be safe.


    Then in another column you would use this column formula to output your list:

    =IFERROR(INDEX(DISTINCT(COLLECT({Company Name Column}, {Projects Column}, HAS(@cell, "GO 2"))), [Helper Column]@row), "")

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!