Populate/Select a Contact (from a list) depending on the value selected in another cell


I have a problem I can't quite solve and I'm not sure if it's just not possible:

I have a 'Responsible' column that is a list of Contacts. I have another column called 'Site' that is a drop down list. Depending on what 'Site' is selected I'd like the corresponding 'Responsible' to be populated.

Example: If Site 'NYC' is selected then the contacts should be 'NYCtech@email.com'. If the SITE is 'ATL' then 'ATLtech@email.com' and so on.

There's 40 sites so rather than making a very long formula I was hoping I'm just missing the obvious answer.

The Contacts are important as I want to setup automation based on the contact to send reminders, update requests etc.




Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!