If formula evaluating wrong statement

Hi Everyone,

I was hoping I could avoid Support for this one - not sure what I'm getting wrong here.

I have an if statement, if a certain cell states "no" then I want it to return blank, otherwise I want it to evaluate an index-match formula.

What I'm running into is that even when the cell is equal to no, it's still running the index-match formula and turns up a partial match.

I have a testing column with an if statement that again checks if the cell states "no" then it returns blank, otherwise, it will return a "yes". This formula is working perfectly fine.

Does anyone know what is going wrong here?

EE Delegation Period Formula: =IF([EE Delegation Active?]@row = "No", "", (INDEX([EE Delegation Key]:[EE Delegation End], MATCH("Active" + [Employee Email]@row, [EE Delegation Key]:[EE Delegation Key], 0), 67))) + "-" + (INDEX([EE Delegation Key]:[EE Delegation End], MATCH("Active" + [Employee Email]@row, [EE Delegation Key]:[EE Delegation Key], 0), 68))

testing formula: =IF([EE Delegation Active?]@row = "No", "", "Yes")

Best Answer

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Answer ✓

    @Alexandra Stewart Your formula is working perfectly, just not doing what you intended. When it finds a status of "No", it's running the "blank", and then concatenating the blank onto the front of the next formula you list. This is because Everything in BOLD below is not part of the IF Statement.

    =IF([EE Delegation Active?]@row = "No", "", (INDEX([EE Delegation Key]:[EE Delegation End], MATCH("Active" + [Employee Email]@row, [EE Delegation Key]:[EE Delegation Key], 0), 67))) + "-" + (INDEX([EE Delegation Key]:[EE Delegation End], MATCH("Active" + [Employee Email]@row, [EE Delegation Key]:[EE Delegation Key], 0), 68))

    So what you need to do is wrap everything after "No", "", in parentheses and close out the IF at the very end. This makes the entire "INDEX + "-" + INDEX" into the false condition of the IF. You'll know it's right if the very last end parentheses is the same color as the very first open parentheses (the one right after =IF).

    Regards,

    Jeff Reisman, IT Business Analyst & Project Coordinator, Mitsubishi Electric Trane US

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

Answers

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Answer ✓

    @Alexandra Stewart Your formula is working perfectly, just not doing what you intended. When it finds a status of "No", it's running the "blank", and then concatenating the blank onto the front of the next formula you list. This is because Everything in BOLD below is not part of the IF Statement.

    =IF([EE Delegation Active?]@row = "No", "", (INDEX([EE Delegation Key]:[EE Delegation End], MATCH("Active" + [Employee Email]@row, [EE Delegation Key]:[EE Delegation Key], 0), 67))) + "-" + (INDEX([EE Delegation Key]:[EE Delegation End], MATCH("Active" + [Employee Email]@row, [EE Delegation Key]:[EE Delegation Key], 0), 68))

    So what you need to do is wrap everything after "No", "", in parentheses and close out the IF at the very end. This makes the entire "INDEX + "-" + INDEX" into the false condition of the IF. You'll know it's right if the very last end parentheses is the same color as the very first open parentheses (the one right after =IF).

    Regards,

    Jeff Reisman, IT Business Analyst & Project Coordinator, Mitsubishi Electric Trane US

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

  • Thank you so much for your help! I can't believe I didn't even catch that.

    I hope you have a good rest of your day!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!