 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…

Functionality
Industry 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)

=[cell2] * [cell4] / [cell5]

Hope this helps

Andreas In reply to by Andreas S.

just learned that non Germans (maybe not all?) have to use komma instead of semikolon, so if it doesn't work replace the Semikolon... 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  