Pairing 2 items

Vinton Douglas

I have a set of managers managing different departments. I want to ensure that when a manager is selected, perhaps from a dropdown, it corresponds to the appropriate department. In other words managers and departments should be paired in 2 separate columns or otherwise. Is there a way to do this?

Kelly Moore

Hello @Vinton Douglas

There are a few approaches that will work. Depending on the number of departments/managers - and your inclination of changing formulas if a person changes roles, one can use nested IF statements.

The other option is to set up a reference sheet since the Departments will remain fixed and the people may or may not. This is my preference when I have your situation. If the person changes, you are then updating a name presented in tabular format, rather than updating a formula.

An Index/Match (or Index/Collect) would then pull the relevant data into the field on your main sheet.