How to make specific person from a department a contact automatically

Jonathan Forrester

Hello all! I have a question where I am not understanding the total scope of formulas and functions and would like help to see what could assist me. I am a Customer Success Manager and I create projects for my customers that are onboarding. What im trying to do is somehow label certain people who are the CSM, Sales, Hardware, etc and be able to add them as the internal contact for every cell that has that department on it. Attached is an example if I didnt explain it well.

You can see above that under internal owner, I am labeled at the top as the CSM but im wanting that to also populate in the cells below if possible.

Sean Morgan

Hello Jonathan,

Thank you for reaching out within the Community!.

I feel the best formula for this situation would be a VLOOKUP. This allows you to search for a criteria on a different sheet, and return the corresponding value.

Here is my example of what can be achieved below. Please note that this is an example and will require modification to work within your setup:

This is my main sheet, similar to yours. Anytime I enter a department, my formula looks at a different sheet and will return the correct Owner. The formula I used was =VLOOKUP(Department@row, {Vlookup Range 1}, 2, false)

This is the separate sheet where I store Departments and the related Owner.

If you have multiple people for Sales, Hardware etc, you could alter the titles under both Department columns to something like Hardware Agent 1 or Hardware Rep 1.

Please see more on the VLOOKUP function here: https://help.smartsheet.com/function/vlookup

Feel free to let me know if you have any questions or run into any issues. If your formula returns an error, you may want to look at our Formula Error article here: https://help.smartsheet.com/articles/2476176-formula-error-messages

Kindest Regards

Sean