Trying to create an IF THEN function in smartsheet
My example is to select an material size value based on the material. Something like this:
If [cell1]="4X8 Sheet" then [cell2]=32, if [cell1]="4X12 Sheet" then [cell2]=48, etc, etc.
I have a dropdown list in the materials column to restrict the set of possible materials.
Subsequently, I would like to take those values to calculate productivity rates(averages) in another column using the values calculated for cell 2. Something like this:
If [cell2]="32" then [cell3]=32 * [cell4] / [cell5], if [cell2]="48" then [cell3]=48 * [cell4] / [cell5], etc. etc.
I tried using the LOOKUP formula using a lookup table from another help post, but it didn't work. I also tried a nested formula, but it didn't work either. Here's the post I'm referring to: https://community.smartsheet.com/discussion/iwantutiliseifthenfunctionsmartsheet
Comments

Hi Horace,
If your material list is not to long it´s maybe easiest to enter something like this into [cell2]:
=If([cell1]="4X8 Sheet";32; if([cell1]="4X12 Sheet";48; if(.....)))
(the "32" is the "then" for the first if, everything after 32 is the "else" for the first if)
Your [cell3] needs this:
=[cell2] * [cell4] / [cell5]
Hope this helps
Andreas

just learned that non Germans (maybe not all?) have to use komma instead of semikolon, so if it doesn't work replace the Semikolon...

I had to try several things, but think I figured it out. A couple of corrections to the formulas you mention above. Would you please confirm that this is correct?
1. Cell name does not need to be in [ ] brackets
2. commas should replace the semicolons in the above formula you mention...
Is that right?
Here's what I used that seems to work: =IF(Material1 = "4X8 Drywall", 32, IF(Material1 = "4X9 Drywall", 36), if(....)))
Thank you

That comma / semicolon thing is a nightmare, I have to use semicolon, if yours is happy with comma then that's what has to be in your region.
The [ brackets should always be OK but not always needed.
You will need one If for each of your products.

Thank you!

How many different materials do you have? There are a few other ways this could be set up to provide more efficiency and flexibility if you have a longer list of materials. My general rule of thumb is 5 or more get converted into a table instead of a nested IF statement.
Help Article Resources
Categories
Check out the Formula Handbook template!