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

Options
edited 12/09/19

Hi,

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

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

1-200

201-400

401-600

>600

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?

Thanks,

Tags:

• ✭✭✭✭
Options

Heidi,

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.

Hope this helps.

-- Kent

• Options

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

• Options

Heidi,

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,

Jason

This discussion has been closed.