I want to utilise an IF THEN function in Smart sheet

Claire Malik

My example is to select a cost rate based on the employee title.  

 

If [cell1]="welder" then [cell2]=80, if [cell1]="crane operator"then [cell2]=100, etc, etc. 

Greg Gates

There are two ways you could do this! One is using nested IF-statements (https://www.smartsheet.com/blog/support-tip-build-nested-IF), the other way is to use the new LOOKUP functionality. LOOKUP is probably cleaner in the end, but it's less intuitive to use. 

 

1) Using Nested-IF Statements your formula would look something like this:

=IF(Job1="Welder", 80, IF(Job1="Crane Operator", 100, IF(Job1="Foreman", 120, ....)))

 

Basically, if the IF-statement is true you use the number, otherwise you move on to the next IF-statement.

 

2) Using a LOOKUP table, your smartsheet would look something like this:

 

The first two columns makeup what is known as a lookup table. These are basically constant, never-changing values you might want to reference somewhere else in your sheet. Now, in my column called "Text 2" you can calculate the value of whatever is provided in "Text 1" using the LOOKUP formula:

=LOOKUP([Text 1]1, $Position$1:$Number$4, 2, false)

 

The first parameter here is the value you're looking up, the second paramter is the range of cells that makeup your Lookup Table, the third parameter is the number of the coulmn you want to return, and the final parameter tells Smartsheet if your table is in alphabetical order or not. The dollar signs are used to prevent the formula from automatically incrementing when you copy and paste it into new columns.

 

I think either of those should solve your issue! Let me know if you need more help, or if I didn't understand your scenario correctly!

J. Craig Williams

Good job Greg on thinking beyond the wording of the original question.

Always good to be reminded of the new features -- and how they can simplify things if implemented well.

 

Craig