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/i-want-utilise-if-then-func…



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



In reply to by Andreas S.

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 semi-colons 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




In reply to by Horace

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. 


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.