Index Match mystery

Options

Good afternoon,

I have an index/match combo that is working perfectly well BUT FOR, the top row of my sheet. Even when the field it references is blank, it stays checked.

Backstory: my source sheet is a matrix that has our roles down column 1and our systems across the top in the column headers. Whenever a role requires access to a system we manually put a check in the box. In my Target sheet I have a formula whose purpose is to look at a particular role/system combo and return a checkmark if the matrix sheet A says it needs one. For some reason it returns a checked box when the "role" field is blank...Why is this happening? If there is not a role entered in the role column I need it to be not checked so it doesn't trigger various workflows. Thanks in advance! -Shelley

ps- the top 2 locked rows are just there as place holders so that when a form is submitted the formulas in other fields are maintained.



Answers

  • SPRINGBOK
    SPRINGBOK ✭✭✭
    Options

    Hello again...I figured it out (I think)...the formula I had for the row in question was actually =IFERROR(INDEX({master role/system Range 3}, MATCH([NEW ROLE]$4, {master role/system Range 2}, 0), MATCH([SYSTEM BEING ADDED/ MODIFIED]@row, {systemaccrosstop}, 0)), "X") and the X was causing the box to stay checked I think 🤦🏼‍♂️...Thanks!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!