Help! Nested IF statement with multiple conditions

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!

Best Answer

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    Answer ✓

    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:


    PMP Certified

    [email protected]

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

Answers

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    Answer ✓

    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:


    PMP Certified

    [email protected]

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • @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
    Bassam Khalil ✭✭✭✭✭✭
    edited 07/19/21

    @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

    PMP Certified

    [email protected]

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!