CONTAINS or HAS formula extension

Options

Hi community,

I am looking to extend either a CONTAINS or HAS formula to pick up a dropdown value from one column and assign it a numerical value in a separate column. Can anyone assist in what is the most effective way to do so?

The intention is when any value from the "Suburb Population" column is selected via dropdown it will assign the applicable score in the "Population Ranking" column. Can anyone assist?


=IF(CONTAINS("20K +", [Suburb Population]@row), 10) or

=IF(HAS("20K +", [Suburb Population]@row), 10)

Answers

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    Options

    Hi @JMOHI

    Hope you are fine, i advise you to use HAS function in your exercise because it's Search for an exact match of a value but you need to mention the exact value in the search criteria for this function for example:

      =IF(HAS("18K-20K", [Suburb Population]@row), 9)
    

    i don't prefer to use CONTAINS Function here becuse it's Used within another function to search for a character or string.

    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • JMOHI
    JMOHI ✭✭
    Options

    Thanks @Bassam Khalil well noted, how would I extend this formula to produce a value for all "Suburb Population" values?

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    Options

    @JMOHI

    If you can share me on a sample copy of your sheet as an admin I can create the exact formula for you then you can copy it to your original sheet.

    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!