Help! Nested IF statement with multiple conditions

Mylene Nafarrete

Hi, I really need help or guidance with the condition below. I have an existing excel report that I would like to transition it into Smartsheet but this formula is really driving me up the wall and I just can’t figured out what I’m doing wrong! Works well in excel but not in Smartsheet. I’ve also tried to break it down by each statement and it only works on the first condition and the rest I would get a result of #Incorrect Argument or #Unparseable.

Here's what I’m trying to do.

If the Incentive Group = % Net New and TOTAL NET NEW FY END 2021 is less than Silver then “None”,

If the Incentive Group = % Net New and TOTAL NET NEW FY END 2021 is greater than or equal Silver and less than Gold then “Silver”,

If the Incentive Group = % Net New and TOTAL NET NEW FY END 2021 is greater than or equal Gold and less than Platinum then “Gold”,

If the Incentive Group = % Net New and TOTAL NET NEW FY END 2021 is greater than Platinum then “Platinum”,

Otherwise, if Incentive Group = Per Store Growth then VLookup National Incentive with lookup between Per Store $1: NAFA$5

Here’s the formula in Smartsheet:

=IF(AND([Incentive Group]@row = "% Net New", [Total Net New FY End 2021]@row < ROUND(Silver@row, 0)), "None", IF(AND([Incentive Group]@row = "% Net New", [Total Net New FY End 2021]@row >= ROUND(Silver@row, [Total Net New FY End 2021]@row < ROUND(Gold@row, 0)), "Silver", IF(AND([Incentive Group]@row = "% Net New", [Total Net New FY End 2021]@row >= ROUND(Gold@row, [Total Net New FY End 2021]@row < ROUND(Platinum@row, 0)), "Gold", IF(AND([Incentive Group]@row = "% Net New", [Total Net New FY End 2021]@row > ROUND(Platinum@row, 0)), "Platinum", IF([Incentive Group]@row = "Per Store Growth", VLOOKUP([National Incentive Count]@row, $[Per Store]$1:$NAFA$5, 3, 0))))))))

Any guidance and assistance is much appreciated and thanks in advance!

Bassam Khalil

Hi @Mylene Nafarrete

Hope you are fine, please try the following formula:

=IFERROR(IF(AND([Incentive Group]@row = "% Net New", [Total Net New FY End 2021]@row < ROUND(Silver@row, 0)), "None", IF(AND([Incentive Group]@row = "% Net New", [Total Net New FY End 2021]@row >= ROUND(Silver@row, 0), [Total Net New FY End 2021]@row < ROUND(Gold@row, 0)), "Silver", IF(AND([Incentive Group]@row = "% Net New", [Total Net New FY End 2021]@row >= ROUND(Gold@row, 0), [Total Net New FY End 2021]@row < ROUND(Platinum@row, 0)), "Gold", IF(AND([Incentive Group]@row = "% Net New", [Total Net New FY End 2021]@row > ROUND(Platinum@row, 0)), "Platinum", IF([Incentive Group]@row = "Per Store Growth", INDEX($[Per Store]$1:$NAFA$5, MATCH([National Incentive Count]@row, $[Per Store]$1:$[Per Store]$5), 3)))))), "")

the following screenshot shows the result:

Bassam Khalil

Hi @Mylene Nafarrete

Hope you are fine, please try the following formula:

=IFERROR(IF(AND([Incentive Group]@row = "% Net New", [Total Net New FY End 2021]@row < ROUND(Silver@row, 0)), "None", IF(AND([Incentive Group]@row = "% Net New", [Total Net New FY End 2021]@row >= ROUND(Silver@row, 0), [Total Net New FY End 2021]@row < ROUND(Gold@row, 0)), "Silver", IF(AND([Incentive Group]@row = "% Net New", [Total Net New FY End 2021]@row >= ROUND(Gold@row, 0), [Total Net New FY End 2021]@row < ROUND(Platinum@row, 0)), "Gold", IF(AND([Incentive Group]@row = "% Net New", [Total Net New FY End 2021]@row > ROUND(Platinum@row, 0)), "Platinum", IF([Incentive Group]@row = "Per Store Growth", INDEX($[Per Store]$1:$NAFA$5, MATCH([National Incentive Count]@row, $[Per Store]$1:$[Per Store]$5), 3)))))), "")

the following screenshot shows the result:

Mylene Nafarrete

@Bassam Khalil - YOU ARE THE BEST! OMG, you are a life saver! Thank you very much for your assistance. I didn't even think about using IFERROR.

Bassam Khalil

@Mylene Nafarrete

Thanks for the nice words, I will be happy to help you any time.

and I will be grateful for your "Vote Up" or "Insightful"

Regards