Welcome to the Smartsheet Forum Archives

The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

Formula Help please

Heidi Decker1
edited 12/09/19 in Archived 2017 Posts


I need a formula that will do the following please...

In the field "How Many" they will select from a drop down list





I will run the formula in the Cleaning Fee column.

Depending on which one they select, I need values to go into the Cleaning Fee column

So for 1-200 the value would be 150.00

201-400 would be 300.00, 401-600 would be 450.00 and >600 would be 600.00.

Any help?




  • Kent Vaughn
    Kent Vaughn ✭✭✭✭



    There are a few different ways to solve this.  I'm not an expert, so others might have another solution.  But one solution is to use the LOOKUP() function.   The cavet to this solution is that, to the best of my knowledge, you have to include the Lookup table in the same sheet where the lookup function is executed.   My suggestion would be to add your lookup table to some new columns you add to the far left of your sheet.  You can even hide these columns if you need to.   I've included a screen shot which shows how I laid out the table and the forumula.    


    Online Help for LOOKUP function can be found HERE


    Hope this helps.  


    -- Kent 

    SS Solution 31JAN17.jpeg

  • Michael G.

    Hi Heidi,


    an alternative would be nested IF statements:


    =IF([HowMany]1 < 201;150; IF([HowMany]1 < 401;300;IF([HowMany]1 < 601;450;600)))


    Advantage: No separate or hidden columns.

    Disadvantage: Not so user friendly to update the fee (if necessary).


    Good luck

  • Jason Rowe


    I like Michael's idea of using nested If statements. However if your "How Many" column is going to be a drop down list, you may need to write your formula like this.

    =IF([How Many]1 = "1-200", 150, IF([How Many]1 = "201-400", 300, IF([How Many]1 = "401-600", 450, IF([How Many]1 = ">600", 600, " "))))


    Hope this helps,



This discussion has been closed.