Automation where condition pulled in via Index(Match)

I'm trying to get an automation to run where the condition is based on a column not being blank but that column gets filled in via an Index(Match) formula and I think because of this, the cell is always showing as blank when triggered and so it is automatically going to the "otherwise" condition despite "1st Approver" column not being blank. I'm trying to figure out either a way to restructure my automation or something else. Any thoughts?



  • Julie Fortney
    Julie Fortney Overachievers

    @JLen, first let's look at your index/match formula:

    If a match is not found on your reference sheet, does it return #NO MATCH in the cell? If so, try wrapping it in an IFERROR formula and specify the value you would like it to display it if doesn't find a match.

    Example: =IFERROR(Your Index/Match Formula Here, "No Match Found")

    Then you could set up your automation to trigger when Rows are Added or Changed, when 1st Level Approver approval changes to any value, then set your conditions. The first condition would be 1st Level Approver approval is "No Match Found", and the second could be 1st Level Approver approval is not "No Match Found".

    Let me know if you want to give that a try and have any questions.

  • JLen
    JLen ✭✭✭

    Hi @Julie Fortney - thank you so much for taking the time to respond. I actually figured out my error, one of the dumbest I've made. :) In the above image, the condition that references the "1st Level Approver Approval" - that was the wrong column, it should have been "1st Level Approver." This was a good lesson in naming columns better to avoid confusion like this. I won't admit how long this took me to figure. I'm grateful for the time you took to respond.

  • Julie Fortney
    Julie Fortney Overachievers

    @JLen - I am glad you got it figured out! I have made my fair share of small errors that are so obvious I don't even see them.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!