Formula to list department name when individuals name is listed in another column

Hello!

Formula help for the following scenarios:

Scenario 1: When Jess Baker is selected in "stakeholder lead" column (multi select enabled), the "department" column automatically populates to "MEQA"

Scenario 2: When Dan Striker is selected in "stakeholder lead" column (multi select enabled), the "department" column automatically populates to "ME Ops"

Thanks!

Answers

  • Scott Orsey
    Scott Orsey ✭✭✭✭✭

    Hi @sjohanson , This will depend on how many names you will be doing this with. If it is a reasonably small list of names, you could simply nest a bunch of IF() statements. Use HAS() when looking up the name in the column and then set the column to the department name that you want when the condition is met.

    There are other options which could include creating a separate sheet that has the names in one column and the department in a second column. Then you could use cross sheet references using INDEX/MATCH. This won't immediately work with a multiselect contact column though because you don't know which of the names selected to match. If you can restrict your multiselect to just one name (single select), or if you can always reference only, say, the first name in the list, then you could do this. A lot to explain... so give this a go and write back if you need more instruction.

    Good luck and be well!

    If my response was helpful or answered your question please be sure to upvote it, mark it asawesome, or mark it as the accepted answer!

  • Hello Scott,

    Appreciate the response! Can you write out what the IF() formula would be, if I decide to keep the list of names small?

  • Hi Scott - Following up on this.

  • Sam_Harwart
    Sam_Harwart ✭✭✭✭✭

    @sjohanson

    Scott gave good ideas on more scalable solutions but if the list is only Jess and Dan, you could do something like this:

    [Department]: =IF(HAS([Multi Name]@row, "Matt Lynn"), "MEQA", IF(HAS([Multi Name]@row, "Sam Harwart"), "ME Ops", "No Department Found"))

    You'll need to swap out Matt & Sam for Jess & Dan. This formula also doesn't account for what would happen if both Jess & Dan are selected, but I added a "No Department Found" if both are missing.

    Thanks,
    Sam

    Want to chat about a Smartsheet problem you're facing? Grab time on my calendar here: Schedule a Discovery Call!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!