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

sjohanson

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!

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!

sjohanson

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?

sjohanson

Hi Scott - Following up on this.

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.