Trouble with Nested IF Statements

allison.vess47261

I have to columns in my Smartsheet.  

Once column called "Activity" that is a drop-down with 10 values.

Another column called "Value" where I am putting my Nested IF formula. Basically I want a number to be returned in the Value column depending upon the selection of the drop-down in the Activity column.

To test just one IF statement first, I have this one which works just fine

=IF(Activity1 = "Hosted Upgrades", 5)

However, when I added a second IF statement just to test with two IF statements, this does NOT work

=IF(Activity1 = "Hosted Upgrades", 5, IF (Activity1 = “Content Migrations”, 4))  

Can you help?

Mike Wilday

It looks like you might have created that statement in a word-processing program. When I copied and pasted it there was evidence of SMART QUOTES which word processors frequently convert. Smartsheets will throw an error if it encounters them. Always create your formulas in a basic text editor like notepad, notepad++, TextEdit, or something of that sort.

=IF(Activity1 = "Hosted Upgrades"​​​​​​​, 5, IF(Activity1 = "​​​​​​​Content Migrations"​​​​​​​, 4)) 

allison.vess47261

Thanks Mike and you have amazing eyesight!   I would never had guessed it had anything to do with the type of quotes used.  I opened up Notepad and type it in again, noticed the smartquote issue, copied and pasted this into SmartSheets and again it didn't work. I keep getting the #UNPARSEABLE error. 

Any other suggestions? 

SYSPK

Try adding empty quotes after your second IF statement:

=IF(Activity1 = "Hosted Upgrades", 5, IF(Activity1 = "​​​​​​​Content Migrations"​​​​​​​, 4,"")) 

L_123

No, you don't need to finalize an if statement, it defaults to blank. The issue is that there is a space between the second if statement and the parenthesis

allison.vess47261

Thank you both but neither of these worked.   I'm at a loss.

These formulas are much easier in Excel and Google Sheets.

Paul Newcome

Luke: Beat me to it.

 

Allison: If you have 10 different options, that is going to be a rather looooooong nested IF statement. You may want to consider building a table and using a VLOOKUP function to populate the numbers. It will be a much shorter formula, and the table can either be hidden somewhere on your sheet or even x-sheet referenced from another sheet.

allison.vess47261

Thank you Paul.  I looked up Vlookup, but the definition is "Looks up a value and returns a corresponding value in the same row but from a different column.  I actually want to return a value from a different row.

If this will still work, can you help me with the syntax?

I have a column called "Activity" that has 10 options in the drop-down and I have another column called "Value" where I want it to return a numeric value in the corresponding row depending upon which option was selected in "Activity".   Thanks in advance.

Mike Wilday

Can you share a sample of your sheet with the column titles so that we can see what we're dealing with? Did you copy and paste my formula exactly? Or did you retype it? Seeing a screenshot of the actual spreadsheet will help us to troubleshoot. Your if statement appears to be set up correctly, but unparseable is usually because of an incorrect column name or using the wrong case for a column name. 

https://help.smartsheet.com/articles/2476176-formula-error-messages#unparseable

Paul Newcome

First, set your table up (wherever). For this example I will just use 2 helper columns labelled "ActivityH (Activity Helper) and ActivityP (Activity Points). I'm only going four rows to keep the example simple. Add as many as you need.

 

ActivityH              ActivityP

Something               1

Something Else       2

This                         3

That                         4

 

 

In the Value column put:

 

=VLOOKUP(Activity@row, ActivityH:ActivityP, 2, false)

 

What this does is takes the value that is in the Activity column of whatever row the formula is in. It will then look at the first column of your designated table and return the corresponding value from the second column.

 

Using the table will greatly increase the flexibility. You can add, remove, or change whatever you need to, and as long as the value in the Activity column is in the leftmost column of your table, it will always return the value that you have designated in the second column of your table. If you were to make any changes whether it be adding activities in the first column or adjusting the points in the second column, you no longer have to worry about trying to find exactly the right spot in a massive formula and accidentally fat fingering something. Just throw it in your table and done.

L_123

https://app.smartsheet.com/b/publish?EQBCT=53a0046e91c641eeb5ed5a6da567952e

 

Something like that is what Paul is thinking. I posted 3 different formulas in the sheet, my personal favorite is number 2. It will always know the correct field even if you add to it.

For the other two formulas if you change the table you'll have to change the formulas unless you add to the table by inserting a row in the middle of it. Which is just a hassle.

The only thing about the second formula is you have to make the parent child reference by indenting all of the rows in your table.

Paul Newcome

For the other two formulas if you change the table you'll have to change the formulas unless you add to the table by inserting a row in the middle of it. Which is just a hassle.

 

That's why I try to put the table in its own columns and use full column references. Just add it to the bottom of the list and move on. 

 

If I only have 2 pretty straightforward columns in my table (such as above) I use VLOOKUP as it is just a little easier to plug everything in than INDEX/MATCH. I save that for the multi-column/more complex tables/formulas where I need to factor in blanks and all of that other mess.

 

I do like the IFERROR as well. I hadn't thought that far ahead just yet. Haha

L_123

I used index match because I wanted to use the children reference and couldn't come up with a way to do it inside of a vlookup. As a general rule I try to keep my number of columns as low as possible, though you are right it would be easier to do it that way.

Beyond that, using Index/Match instead of vlookup is a habit that i've gotten into. Vlookup killed one of my sheets before, and I realized index/match has the same capability and is a lot more stable. I haven't used it in an internal sheet since.

allison.vess47261

Thank you Paul so much! This worked and I appreciate your time.   Is there a way to automatically have the formula copied in my Value column in each new row so I don't have to manually do it?  

Thanks again.

allison.vess47261

Thank you Paul and Luke! I will keep your tips in mind.

Paul Newcome

That is default for Smartsheet. Once you drag-fill the formula down, new rows will automatically have it in there.