How to automatically update the contact when the status is "Submitted"

I have two different sheets. Sheet A is a list of contacts by office/division. Sheet B is a sheet that contains an approval path, based on the office/division. I want to use a formula to automatically pull in the correct contact person into Sheet A from Sheet B, only if the status on the Sheet A is "Submitted". If the status is not "submitted" I do not want the contact person to update, but stay who the original contact was. This is so that we can easily update the contact person on our sheets if someone is out of the office, or if staffing changes. The current formula I am using is below. It is correctly updating when the status is "Submitted", however, I cannot figure out how to prevent it from updating other cells where the status is not "Submitted". I have tried many different formulas with no luck. The image below shows that the contact person is being removed if the status is not "submitted".

=IF([Program Monitor Status]@row = "Submitted", INDEX({Copy of Office/Program Staff Directory Range 3}, MATCH(Program@row, {Copy of Office/Program Staff Directory Range 4}, 0)))

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!